ROW_NUMBER, RANK, DENSE_RANK
Number and rank rows within partitions, and pick the right ranking function for ties.
Three Numbering Functions
Three window functions that number rows within a partition:
ROW_NUMBER()— 1, 2, 3, 4 (always unique)RANK()— 1, 2, 2, 4 (ties share, then skip)DENSE_RANK()— 1, 2, 2, 3 (ties share, no skip)
ROW_NUMBER
Assigns 1, 2, 3 within each partition. Ties are broken arbitrarily — provide a tie-breaker in ORDER BY for determinism:
SELECT id, user_id, total,
ROW_NUMBER() OVER (
PARTITION BY user_id ORDER BY total DESC, id
) AS rn
FROM orders;All lessons in this course
- OVER, PARTITION BY, ORDER BY
- ROW_NUMBER, RANK, DENSE_RANK
- LAG, LEAD and Time-Series Patterns
- Running Totals and Moving Averages