How do you get the top N rows per group in SQL?
How do you get the top N rows per group in SQL?
Use a window function to number rows inside each group, then filter on the number. This is the most portable pattern.
-- top 3 products by revenue in each category
SELECT category_id, product_id, revenue
FROM (
SELECT category_id, product_id, revenue,
ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY revenue DESC, product_id) AS rn
FROM product_sales
) t
WHERE rn <= 3;
| Function | Result |
|---|---|
ROW_NUMBER |
exactly N rows per group, ties broken by the tie-breaker |
RANK |
N places; more than N rows if there are ties |
DENSE_RANK |
N distinct values; more rows when there are ties |
LATERAL / APPLY (can stop early and use an index per group):
Đăng nhập để tham gia thảo luận cùng mọi người.
Chưa có bình luận nào
Hãy là người đầu tiên chia sẻ suy nghĩ của bạn.