CODEWITHSUNDEEP
sqlsql-servert-sql
hieko
hieko

Reputation: 393

select count wise records till a limit

I have a data table like below and I want to update column flag in that based on below mention condition. 

declare @tbl table (id int identity  ,supergroup int ,subgroup int , flag char(1)  )

The objective is to fetch top 40 records in total based on supergroup and subgroup like 40/count(distinct subgroup ) where supergroup = @subgroup till 40 records. suppose there are 3 supergroup and if for supergroup 1000 there are 3 distinct subgroup then fetch 40/3 =13 records and for next supergroup 2000 there are again 3 distinct subgroup then fetch 13 records from that, for next supergroup 3000 if there are again 3 subgroup then 40/3=13 logic will sum up only 39 so I need 14 from the last supergroup.

in any scenario, I need exact 40 records.

Upvotes: 0

Views: 72

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269963

If I understand correctly, you can do:

select top (40) t.*
from @tbl t
order by row_number() over (partition by supergroup, subgroup order by newid());

This will do a round-robin selection from the supergroups and subgroups, returning one row from each before getting a second row from the same group.

EDIT:

If you need to do a round-robin of the supergroups first, then I think this will do that:

select top (40) t.*
from @tbl t
order by row_number() over (partition by supergroup order by subgroup, newid());

However, this is likely to get all the results from one subgroup. Let's try again:

select top (40) t.*
from @tbl t
order by row_number() over (partition by supergroup order by newid()),
         row_number() over (partition by supergroup, subgroup order by newid());

Upvotes: 2

Related Questions