0

For an auditing project, need to select at random three tracking IDs per associate and cannot be dups. Wondering if it's possible with SQL?

Sample SQL Server Data:

Associate Tracking ID
Smith, Mary TRK65152
Smith, Mary TRK74183
Smith, Mary TRK35154
Smith, Mary TRK23117
Smith, Mary TRK11889
Jones, Walter TRK17364
Jones, Walter TRK91736
Jones, Walter TRK88234
Jones, Walter TRK80012
Jones, Walter TRK55874
Williams, Tony TRK58142
Williams, Tony TRK47336
Williams, Tony TRK13254
Williams, Tony TRK28596
Williams, Tony TRK33371
Dale K
  • 25,246
  • 15
  • 42
  • 71
Manny805
  • 7
  • 2
  • 1
    Does this answer your question? [Select n random rows from SQL Server table](https://stackoverflow.com/questions/848872/select-n-random-rows-from-sql-server-table) – Dale K Jul 30 '21 at 05:45
  • 1
    Please refer this. [How to request a random row in SQL?](https://stackoverflow.com/questions/19412/how-to-request-a-random-row-in-sql) – UDS Jul 30 '21 at 06:13

1 Answers1

4

You may use ROW_NUMBER here with a random ordering:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY Associate ORDER BY NEWID()) rn
    FROM yourTable
)

SELECT Associate, TrackingID
FROM cte
WHERE rn <= 3;
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360