sql / intermediate
Snippet
Window Functions with ROW_NUMBER
Window functions perform calculations across a set of table rows related to the current row. ROW_NUMBER assigns a unique sequential integer to rows within a specific partition.
snippet.sql
1
2
3
4
5
6
SELECTproduct_name,category,price,ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) as rankFROM products;
Breakdown
1
ROW_NUMBER() OVER (
The window function used to assign a ranking integer.
2
PARTITION BY category
Resets the ranking count for every distinct category.
3
ORDER BY price DESC
Determines the order of ranking within each category partition.