Ash Ash - 5 months ago 10
SQL Question

Temp table - group by - delete - keep top 10

I have a temp table with 50 000 records. If I do a

GROUP BY
, with COUNT, it will look like this:

+--------+--------+
|GrpById | Count |
+--------+--------+
| 1 | 10000 |
| 2 | 8000 |
| 3 | 12000 |
| 4 | 9000 |
| 5 | 11000 |
+--------+--------+


I would like to delete some records, so from each Id's (1,2,3,4,5) I would have only 10 records left after deletion.

So eventually If I would make a new
GROUP BY
with COUNT, I would have something like this:

+--------+--------+
|GrpById | Count |
+--------+--------+
| 1 | 10 |
| 2 | 10 |
| 3 | 10 |
| 4 | 10 |
| 5 | 10 |
+--------+--------+


Can I do it without
FETCH NEXT
?

Answer

To just preserve an arbitrary 10 per group you can use

WITH CTE AS
(
SELECT *,
       ROW_NUMBER() OVER (PARTITION BY GrpById ORDER BY GrpById) AS RN
FROM YourTable
)
DELETE FROM 
CTE WHERE RN > 10;

Change the ORDER BY if you need something less arbitrary.