开发者

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 i开发者_开发知识库s compatibility mode 80 on SQL Server 2005


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
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜