Top-N per group (MSSQL)

Question

I have 10k - 1m goods wich are discribed by fields product_id, name, category, price. Which is the fastest way to fetched 10 most expensive goods from each category? Previously I checked this answer https://stackoverflow.com/a/176985/9513268. My table:

-------------------------------------
|product_id| name | category| price |
-------------------------------------
|    1     |Phone | Gadgets | 599.99|
------------------------------------
|    2     |Jacket| Clothes | 399.00|
------------------------------------- 
|   ...    | ...  |   ...   | ...   |
-------------------------------------

So what was wrong with the answers there? Why didn't your attempt work? This feels like it's just a duplicate of that question. — Thom A, May 09 '20 at 13:39
@Larnu I thought maybe there’s a quicker way to accomplish the task. — Mr. 13, May 09 '20 at 13:50
Considering that the answer you accepted is the same as the answer you have linked, I've closed this as a duplicate of said answer. — Thom A, May 09 '20 at 14:07

score 0 · Accepted Answer · answered May 09 '20 at 13:52

You can use window functions, as showned in the answer that you linked.

select *
from (
    select t.*, rank() over(partition by category order by price desc) rn
    from mytable t
) t
where rn <= 10
order by category, rn

The key is to properly define the over() clause of the window function. You want the top 10 by category, so this column goes to the partition by; you want the top most expensive goods, so the order by criteria is on descending price.

You can run the subquery separately and stare and the rn column to better understand the logic.

Top-N per group (MSSQL)

1 Answers1