CODEWITHSUNDEEP
sql-servert-sql
Chris
Chris

Reputation: 1766

SQL server random row for each unique result in column

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

Upvotes: 0

Views: 169

Answers (2)

nharrer
nharrer

Reputation: 636

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.

Upvotes: 0

Rafał Wojtaszek
Rafał Wojtaszek

Reputation: 668

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

Upvotes: 3

Related Questions