Ranking: ROW_NUMBER, RANK, DENSE_RANK, NTILE · SQL: Zero to Production

Four ways to answer "what position is this row in"

All four functions from this lesson require ORDER BY inside OVER() (from the previous lesson — position-dependent functions need it to mean anything). They differ only in how they handle ties — rows with equal values on the ordering column(s).

ROW_NUMBER(): always unique, ties broken arbitrarily

SELECT title, rental_rate,
       row_number() OVER (ORDER BY rental_rate DESC) AS rn
FROM film
ORDER BY rn
LIMIT 8;

ROW_NUMBER() assigns 1, 2, 3, 4, ... with no gaps and no repeats, ever — even when several rows tie on rental_rate, they still get distinct consecutive numbers, in whatever order the engine happens to break the tie (the tie order is arbitrary, not reproducible: if you need that tie-break to be reproducible, add a tiebreaker column to the ORDER BY, e.g. ORDER BY rental_rate DESC, film_id).

RANK(): ties share a rank, then skip

SELECT title, rental_rate,
       rank() OVER (ORDER BY rental_rate DESC) AS rk
FROM film
ORDER BY rk
LIMIT 8;

RANK() gives tied rows the same rank number, then skips the next rank(s) by however many rows tied. If three films tie for rank 1, the next distinct value gets rank 4 (not 2) — the gap accounts for the three rows that shared rank 1. This matches how you'd rank a race with a three-way tie for first: the next finisher is "4th," not "2nd."

DENSE_RANK(): ties share a rank, no skip

SELECT title, rental_rate,
       dense_rank() OVER (ORDER BY rental_rate DESC) AS drk
FROM film
ORDER BY drk
LIMIT 8;

Same tie-sharing behavior as RANK(), but no gap afterward — three films tied for rank 1 means the next distinct value gets rank 2. Use DENSE_RANK() when you want "how many distinct value-tiers are above (or at) this row," rather than a literal competition-style ranking.

Seeing all three side by side — the difference only shows up with ties

SELECT title, rental_rate,
       row_number() OVER (ORDER BY rental_rate DESC) AS rn,
       rank()       OVER (ORDER BY rental_rate DESC) AS rk,
       dense_rank() OVER (ORDER BY rental_rate DESC) AS drk
FROM film
ORDER BY rental_rate DESC, title
LIMIT 10;

Every row with a unique rental_rate gets the same number in all three columns. The columns only diverge on tied rows — this is the entire lesson, made visible in one query.

NTILE(n): divide into n roughly-equal buckets

NTILE(n) doesn't rank individual rows — it assigns each row to one of n buckets, as evenly sized as possible, in the order given by ORDER BY:

-- Split all films into 4 price quartiles.
SELECT title, rental_rate,
       ntile(4) OVER (ORDER BY rental_rate) AS price_quartile
FROM film
ORDER BY rental_rate
LIMIT 10;

This is the standard tool for quartiles/deciles/percentile-style bucketing — "which price quartile is this film in" is a direct, one-line answer with NTILE(4), no manual boundary-value computation needed. If the row count doesn't divide evenly by n, the earlier buckets get one extra row each (Postgres's documented behavior, not something you need to special-case yourself).

Check yourself

Three rows tie for rank 1 under RANK(). What rank does the next (non-tied) row get? What would it get under DENSE_RANK() instead?
Why might ROW_NUMBER() return a different tie-breaking order on two separate runs of the identical query, and how do you make it deterministic?
You need to split a customer list into 10 equally-sized groups for an A/B test. Which of the four functions in this lesson is the right tool?

30. Ranking: ROW_NUMBER, RANK, DENSE_RANK, NTILE