SQL server random row for each unique result in column

Question

I have a table which contains multiple colleague names and their sales transactions. At the end of the day a colleague may have had 100 sale records, I need to pick one at random for each colleague

For example if there were 100 colleagues working that day the result would give 1 random record for each colleague, returning 100 in total

I have looked at newid() but can't figure out how to return 1 record per colleague

Similiar question for PostgreSQL (can be easily adopted for SQL Server) - http://stackoverflow.com/questions/16044828/select-random-row-for-each-group — Ginden, Sep 29 '15 at 21:17

Rafał Wojtaszek · Accepted Answer · 2015-09-29T21:24:35.333

3

You can try something like this:

WITH summary AS (
    SELECT o.id, 
           o.client, 
           o.ammount, 
           ROW_NUMBER() OVER(PARTITION BY o.id
                                 ORDER BY NEWID()) AS rn
      FROM orders o)
SELECT s.*
  FROM summary s
WHERE s.rn = 1

edited Sep 29 '15 at 21:24

answered Sep 29 '15 at 21:21

Rafał Wojtaszek

668
6
10

2

Shouldn't it be `WHERE s.rn = 1`, not `WHERE s.rk = 1`? – Tim Sep 29 '15 at 21:23
Rn is always 1 in this example? – Chris Sep 29 '15 at 21:27

score 0 · Answer 2 · answered Sep 29 '15 at 21:28

Let's say your table is called Sale and it has a columns Salesman_ID, then you could write:

SELECT * 
  FROM Sale
 WHERE Salesman_ID = (SELECT TOP 1 Salesman_ID 
                        FROM Sale Sale2 
                       WHERE Sale2.Salesman_ID = Sale.Salesman_ID 
                       ORDER BY NEWID())

For each unique Salesman_ID you get only a single random row out of the group of rows corresponding to that Salesman_ID.

SQL server random row for each unique result in column

2 Answers2