ROW_NUMBER, RANK and DENSE_RANK: three answers to “what position”

Four students, two of them tied

Scores: Asha 90, Bilal 85, Chen 85, Dev 80. Ask for their positions and there are three defensible answers, and SQL gives you all three as separate functions.

SELECT name, score,
       row_number() OVER (ORDER BY score DESC) AS rn,
       rank()       OVER (ORDER BY score DESC) AS rnk,
       dense_rank() OVER (ORDER BY score DESC) AS drnk
FROM results;

• row_number() gives 1, 2, 3, 4. Every row gets a distinct number. Bilal and Chen are tied, and one of them gets 2 and the other 3 by an order the query did not specify.
• rank() gives 1, 2, 2, 4. Ties share a position, and the next position skips ahead by the number of tied rows, because two people came second and so nobody came third.
• dense_rank() gives 1, 2, 2, 3. Ties share a position and nothing is skipped.

None of these is more correct. A sports league uses rank(): two teams level on points are joint second, and the next is fourth. A “top three products” list that should show every product that made the top three uses dense_rank(). A pagination key or a deduplication step uses row_number(), because it needs every row to have a different number.

row_number needs a complete order

Because row_number() must break every tie, it will break them arbitrarily if you let it. Two runs of the same query can give Bilal 2 then 3. In a report that is a cosmetic wobble; in a query that keeps the row where rn = 1 and deletes the rest, it decides which row survives. Always add a tiebreaker that cannot tie, usually the primary key:

row_number() OVER (PARTITION BY customer_id ORDER BY placed_at DESC, order_id DESC)

Now “the customer’s latest order” is the same row every time, and when two orders share a timestamp the higher id wins, which is a rule you can explain.

Where NULLs rank

Sorting descending puts NULLs first in Postgres, because NULL is treated as larger than every value, and a student with no score would be ranked top of the class. Say what you mean: ORDER BY score DESC NULLS LAST. MySQL and SQLite treat NULL as smallest, so the same query ranks the missing score last, which is a difference that shows up only when the data has gaps.

Cutting into equal groups

ntile(4) OVER (ORDER BY spend DESC) assigns each customer to a quartile: the top quarter of spenders get 1, the next quarter 2, and so on. When the rows do not divide evenly the earlier groups get the extra: ten rows into four tiles gives groups of 3, 3, 2, 2. It is the tool for “split customers into deciles by revenue” and it is also how a lot of segmentation gets done badly, because ntile produces groups of equal count, not equal value. The top decile by count may hold 60% of revenue. If you want groups of equal revenue, you need a running total and a cut at each tenth of the sum, which the running-totals lesson makes straightforward.

Position as a fraction

percent_rank() returns 0 for the first row and 1 for the last, with each row placed by how many rows fall before it. cume_dist() returns the share of rows with a value at or below this one. A student at cume_dist 0.9 scored as well as or better than 90% of the class. These are how “you are in the top 5%” is computed, and they inherit the tie behaviour of rank(), so tied students get the same fraction.

Rank within a group

Add a partition and the ranking restarts in each group:

rank() OVER (PARTITION BY class_id ORDER BY score DESC)

Now every class has its own first place. This is the pattern under every “best in category” question, and the next lesson turns it into a filter.

The check that catches the wrong function

If your ranking is used to pick rows, count the rows it picked. rank() <= 3 can return five rows when there are ties; row_number() <= 3 returns exactly three and may have excluded a tied row on the basis of nothing. Neither is wrong. One of them is what was asked for, and the count is how you find out which you built.