CODEWITHSUNDEEP
sql-server-2005t-sqlsql-server-2000
George Duckett
George Duckett

Reputation: 32428

Get Random Selection of Rows Equal Number Per Grouping

I have a table, SCCalls, which has a column Call_CalT_Code. Call_CalT_Code can be one of several different values. It also has a column Call_InDate which I will be filtering by.

I need to get 1000 random records (filtered by Call_InDate) in total, distributed equally number for each Call_CalT_Code.

How can I do this?

Edit (gbn): solution has to work on a database that is compatibility mode 80 on SQL Server 2005

Upvotes: 1

Views: 110

Answers (1)

gbn
gbn

Reputation: 432210

You may need to play around with the numbers in the division.

If you have 9 values, then 1000/9 = 111 so you'd only get 999 rows back. So I used 1100 and a later TOP 1000. 

SELECT TOP 1000
   *
FROM
   (
   SELECT
       *,
       ROW_NUMBER() OVER (PARTITION BY Call_CalT_Code ORDER BY NEWID()) AS rn
    FROM
       MyTable
      ) foo
WHERE
    rn <= 1100 / (SELECT COUNT(distinct Call_CalT_Code) FROM MyTable)
ORDER BY
    rn

Upvotes: 2

Related Questions