sql / expert
Snippet
Window Functions for Deterministic Ranking
Window functions perform calculations across a set of table rows that are related to the current row. Unlike aggregate functions, they do not group rows into a single output row, preserving the original data detail.
snippet.sql
1
2
3
4
5
6
SELECTproduct_id,category,price,ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) as rank_in_categoryFROM products;
Breakdown
1
ROW_NUMBER() OVER
Assigns a unique sequential integer to rows within a partition.
2
PARTITION BY category
Resets the numbering for each distinct category value.
3
ORDER BY price DESC
Determines the sequence of the numbering based on price in descending order.