I have a simple results set that gives me a User ID and an Operator from a table that contains other columns, one of which is a GUID based on an activity that has taken place for that user. I've added a row number and partitioned by the Operator to get something like this (but much more expansive than this example);
UserId Operator RowNumber
513 J. Smith 12
513 J. Smith 56
513 J. Smith 25
513 J. Smith 45
513 J. Smith 11
513 J. Smith 58
513 J. Smith 22
513 J. Smith 17
513 J. Smith 29
501 S. Jones 33
501 S. Jones 21
501 S. Jones 09
501 S. Jones 15
501 S. Jones 39
501 S. Jones 26
501 S. Jones 31
501 S. Jones 28
501 S. Jones 37
Imagine double the number of rows per operator and about 20 distinct operator names.
I want to be able to return 6 randomised rows per operator in the same results set. I've got as far as getting randomised rows for one operator at a time by enclosing my original query in a sub-query and selecting the TOP 6 from that with a row number and partition and used ORDER BY newid() at the other end. I just can't figure out how to get 6 random rows (based on the row number I've given it) per user all at once.
The trick is to use the order by clause in the ROW_NUMBER declaration:
;WITH CTE AS (
SELECT USERID, OPERATOR
, ROW_NUMBER() OVER(
PARTITION BY OPERATOR
ORDER BY NEWID()) AS RN
FROM [TABLE])
SELECT *
FROM CTE
WHERE RN <= 6