window
ROW_NUMBER / RANK
Window functions that assign an ordinal within a partition. ROW_NUMBER gives every row a distinct sequential integer; RANK and DENSE_RANK repeat values on ties (RANK leaves gaps, DENSE_RANK does not).
ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) | RANK() OVER (...)Parameters / Modifiers
| Parameter | Purpose |
|---|---|
| PARTITION BY | Restart numbering/ranking per group |
| ORDER BY | Ordering that decides position 1, 2, ... |
| DENSE_RANK | Rank without gaps after ties (1, 1, 2 instead of 1, 1, 3) |
| NTILE(n) | Bucket rows into n roughly equal groups |
Examples
SELECT id, ROW_NUMBER() OVER (ORDER BY created_at) FROM users;Global sequence 1..N by signup order
SELECT * FROM (SELECT id, dept, salary, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) rn FROM emp) x WHERE rn <= 3;Top-3 earners per department
SELECT name, score, RANK() OVER (ORDER BY score DESC) FROM tests;Leaderboard where ties share a rank and skip the next
SELECT name, score, DENSE_RANK() OVER (ORDER BY score DESC) FROM tests;Same leaderboard without gaps: 1,1,2 instead of 1,1,3
Dialect notes / Gotcha
ROW_NUMBER is deterministic only if ORDER BY breaks all ties. Window functions require Postgres 8.4+, MySQL 8+, SQLite 3.25+ — older MySQL 5.x has no support at all.
Related functions
MAX
Returns the largest non-NULL value in a group by the column's natural ordering. Useful for latest timestamps, top prices, or alphabetically last strings.
COUNT
Returns the number of rows matching a query, optionally counting non-NULL values of an expression or distinct values. COUNT(*) counts every row including NULLs, while COUNT(expr) skips NULLs.
CASE WHEN
SQL's portable if/else-if expression. Evaluates each WHEN clause in order and returns the first matching result; returns NULL from an unmatched CASE without ELSE.
← All SQL functions · SQL cheat sheet · Window functions · PostgreSQL cheat sheet