I am using Sql Server 2008 R2

I have a existing query that basically says

Select Top 50 Subscriber_ID,  Member_Name, Group_ID

from my_table

order by rand(checksum(newid()))

However the client now wants to have at least two from each group_id. There are 17 different groups.  When I run this as is I get about six of the 17 groups in the results.  How can I change this to get at least two results from each group_id?

Thank you

• Edited by dorindam1 8 hours 50 minutes ago

Select Top 50 Subscriber_ID. Member_Name, Group_ID from (
Select Subscriber_ID. Member_Name, Group_ID,row_number()Over(Partition by Group_ID order by rand(checksum(newid()))) rn

from my_table)
t
 where rn<=3

 order by rand(checksum(newid()))

• Edited by Jingyang LiModerator 5 hours 12 minutes ago

;with cte as (select subscriber_id.Member_Name, Group_ID, row_number() over (partition by Group_Id order by newId()) as RandomNumberInGroup from myTable)

select top 50 * from (select *, case when RandomNumberInGroup IN (1,2) then 1 else 2 end as OrderNo from cte 

) X

order by OrderNo, newId()

-----------

Not sure about execution plan, though.

This gave me seven of the 17 group id's and only one record for two of them.  Closer but still doesn't give me some from all 17 group id's.  Thank you.

What is your latest query? Naomi's solution looks to tick all the boxes from what I can see so you may need to provide some sample data

Logically my solution looks like it should work (I wrote it from the top of my head, not tested), but perhaps due to the order of execution it is still not working.