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
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
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.
