Ranking: ROW_NUMBER, RANK, DENSE_RANK and NTILE

Why ranking functions?

Leaderboards, "top 3 sellers", "the 2nd highest salary" and "the latest order of each customer" all need a number that says where a row stands. You could sort and count by eye, but SQL can write the position on each row for you. That is what ranking functions do.

ORDER BY inside OVER

A ranking function needs to know what "first" means, so you put an ORDER BY inside the OVER brackets. This ORDER BY decides the numbering only. It does not sort the final result; for that you still need the normal ORDER BY at the end of the query. Ranking functions take no argument: you write ROW_NUMBER(), with empty brackets.

Three ways to handle ties

In COMPANY, Anita Desai and Vikram Singh both earn 95000. Watch what each function does with them.

SELECT name, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC, name) AS row_num,
       RANK()       OVER (ORDER BY salary DESC)       AS rnk,
       DENSE_RANK() OVER (ORDER BY salary DESC)       AS dense
FROM employees
ORDER BY salary DESC, name;
-- Meera Iyer     | 250000 | 1 | 1 | 1
-- Rahul Sharma   | 120000 | 2 | 2 | 2
-- Pooja Gupta    | 110000 | 3 | 3 | 3
-- Farhan Ali     | 105000 | 4 | 4 | 4
-- Anita Desai    | 95000  | 5 | 5 | 5
-- Vikram Singh   | 95000  | 6 | 5 | 5
-- Lakshmi Pillai | 80000  | 7 | 7 | 6
-- ... 11 rows

Ranking inside groups

Add PARTITION BY and the numbering starts again at 1 in each partition. This answers "who is the best paid in each department?" in one pass.

SELECT name, dept_id, salary,
       RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS dept_rank
FROM employees
WHERE dept_id IS NOT NULL
ORDER BY dept_id, dept_rank, name;
-- Meera Iyer     | 1 | 250000 | 1
-- Rahul Sharma   | 1 | 120000 | 2
-- Anita Desai    | 1 | 95000  | 3
-- Vikram Singh   | 1 | 95000  | 3
-- Pooja Gupta    | 2 | 110000 | 1
-- ...

NTILE: buckets

NTILE(n) deals the ordered rows into n buckets numbered 1 to n, as evenly as possible, like dealing cards. NTILE(4) gives quartiles: bucket 1 is the top quarter. If the rows do not divide evenly, the first buckets get one extra row. Seven students in four buckets gives sizes 2, 2, 2 and 1.

SELECT name, marks, NTILE(4) OVER (ORDER BY marks DESC) AS quartile
FROM students
WHERE marks IS NOT NULL
ORDER BY marks DESC;
-- Priya Nair  | 91 | 1
-- Divya Kumar | 88 | 1
-- Karan Mehta | 79 | 2
-- Asha Verma  | 76 | 2
-- Ravi Kumar  | 67 | 3
-- Imran Khan  | 58 | 3
-- Arjun Reddy | 45 | 4

NTILE looks only at row positions, not values: two equal marks can fall into different buckets. Also note that NULLs sort first in ascending order in SQLite, so decide on purpose what should happen to them, either filter them out or use NULLS LAST.

PERCENT_RANK and CUME_DIST in one line

PERCENT_RANK() gives (rank − 1) / (rows − 1), a number from 0 to 1 that says what fraction of rows rank below this one. CUME_DIST() gives the fraction of rows at or below this one. With ORDER BY salary, Lakshmi (80000) has a PERCENT_RANK of 0.4 among the 11 employees.

Common mistakes

• Using ROW_NUMBER when ties matter. For "everyone who shares 1st place", use RANK or DENSE_RANK.
• No tie-breaker in ROW_NUMBER, so results change between runs. Add a unique column.
• Expecting the ORDER BY inside OVER to sort the output. Add an ORDER BY at the end of the query too.
• Forgetting PARTITION BY and ranking the whole company when you wanted each department.
• Leaving NULLs in a ranking without thinking. Filter them or place them with NULLS FIRST / NULLS LAST.