sql - Temp table - group by - delete - keep top 10 -

i have temp table 50 000 records. if group by, count, this:

+--------+--------+ |grpbyid | count  | +--------+--------+ | 1      | 10000  | | 2      |  8000  | | 3      | 12000  | | 4      |  9000  | | 5      | 11000  | +--------+--------+ 

i delete records, each id's (1,2,3,4,5) have 10 records left after deletion.

so if make new group by count, have this:

+--------+--------+ |grpbyid | count  | +--------+--------+ | 1      |    10  | | 2      |    10  | | 3      |    10  | | 4      |    10  | | 5      |    10  | +--------+--------+ 

can without fetch next ?

to preserve arbitrary 10 per group can use

with cte ( select *,        row_number() on (partition grpbyid order grpbyid) rn yourtable ) delete  cte rn > 10; 

change order by if need less arbitrary.