I have a table
SELECT a, b, c FROM tab GROUP BY a, b;
| a | b | c | d |
| 1 | 2 | 3 | 100 |
| 1 | 2 | 4 | 110 |
| 1 | 2 | 5 | 90 |
top-n-per-group. One way to do it using
WITH CTE AS ( SELECT a, b, c ,ROW_NUMBER() OVER(PARTITION BY a, b ORDER by d DESC) AS rn FROM tab ) SELECT a, b, c FROM CTE WHERE rn = 1;
(a, b, d, c) should help.
ROW_NUMBER works well when a table has few rows per group and the server has to read most of the table. For example, a table has 1 million rows and 800K distinct groups of
(a, b). You'd have to read most rows any way.
If the table has 1 million rows and only 20 distinct groups of
(a, b) it would be better to do 20 seeks of an appropriate index instead of reading all rows.