Ranking — ROW_NUMBER, RANK, DENSE_RANK

TL;DR

Three nearly-identical functions assign integer ranks within an ordered window:

• ROW_NUMBER() — strictly sequential: 1, 2, 3, 4, … Ties broken arbitrarily (or by extra ORDER BY keys).
• RANK() — ties get the same rank; the next rank skips. 1, 2, 2, 4, 5.
• DENSE_RANK() — ties get the same rank; the next rank does not skip. 1, 2, 2, 3, 4.

For “top K per group” pagination — the most common use — you almost always want ROW_NUMBER. For leaderboards where ties really should share a rank, use RANK. For “what’s the cheapest distinct price?”, use DENSE_RANK.

NTILE(n) is the cousin: bucket rows into n equal-sized buckets. Useful for quartile/decile features.

A worked example

CREATE TABLE scores (player text, game int, score int);
INSERT INTO scores VALUES
  ('Alice', 1, 100), ('Alice', 2, 110), ('Alice', 3, 100),
  ('Bob',   1, 110), ('Bob',   2, 110), ('Bob',   3, 90),
  ('Carol', 1,  95);

Compare all three side by side

SELECT player, score,
       ROW_NUMBER() OVER (ORDER BY score DESC) AS rn,
       RANK()       OVER (ORDER BY score DESC) AS rk,
       DENSE_RANK() OVER (ORDER BY score DESC) AS dr
FROM   scores
ORDER  BY score DESC, player;

+--------+-------+----+----+----+
| player | score | rn | rk | dr |
+--------+-------+----+----+----+
| Alice  |  110  |  1 |  1 |  1 |
| Bob    |  110  |  2 |  1 |  1 |
| Bob    |  110  |  3 |  1 |  1 |
| Alice  |  100  |  4 |  4 |  2 |
| Alice  |  100  |  5 |  4 |  2 |
| Carol  |   95  |  6 |  6 |  3 |
| Bob    |   90  |  7 |  7 |  4 |
+--------+-------+----+----+----+

Read row by row:

• Three rows tie at 110. ROW_NUMBER gives them 1, 2, 3 (the order between ties depends on a secondary sort or just chance). RANK gives all three rank 1 and the next score gets rank 4 (skipping 2 and 3). DENSE_RANK gives all three rank 1 and the next score gets rank 2.
• Two rows tie at 100. Same pattern.
• The differences only show up around ties.

The right choice is question-dependent:

ROW_NUMBER for top-K per group

The single most common ranking pattern in production SQL.

WITH ranked AS (
    SELECT user_id, order_id, placed, total,
           ROW_NUMBER() OVER (
               PARTITION BY user_id
               ORDER BY placed DESC
           ) AS rn
    FROM   orders
)
SELECT * FROM ranked WHERE rn <= 3;

For each user, keep their three most recent orders. The CTE gives every row its rank within its user; the outer filter keeps the top 3. This template solves “most recent N”, “biggest N”, “cheapest N per category”, “latest version per record” — burn it into memory.

Tie-breaking with extra ORDER BY keys

If placed DESC has ties (two orders the same day), ROW_NUMBER picks one arbitrarily. To make it deterministic, add a tiebreaker:

ROW_NUMBER() OVER (
    PARTITION BY user_id
    ORDER BY placed DESC, order_id DESC
)

Now ties are broken by order_id — most-recent or biggest-id wins, and re-running the query gives the same answer.

RANK for leaderboard semantics

SELECT player, total_score,
       RANK() OVER (ORDER BY total_score DESC) AS leaderboard_rank
FROM   (SELECT player, SUM(score) AS total_score FROM scores GROUP BY player) p;

If two players are tied for first, both show rank 1, the next player is rank 3. This matches how leaderboards “feel” — silver doesn’t exist if two players tie for gold.

PERCENT_RANK() is RANK-like but normalized to [0, 1]: (rank - 1) / (n - 1). Useful as a feature: “this player is at the 87th percentile of total score.”

DENSE_RANK for “distinct positions”

“What’s the second-cheapest distinct product price?”:

WITH ranked AS (
    SELECT product, price,
           DENSE_RANK() OVER (ORDER BY price) AS price_rank
    FROM   products
)
SELECT * FROM ranked WHERE price_rank = 2;

If two products tie for the cheapest price, they’re both rank 1 and the next distinct price is rank 2 — which is what “second-cheapest distinct” means. RANK would give the next price rank 3 instead.

NTILE for bucketing

NTILE(n) divides rows into n approximately-equal buckets:

SELECT user_id, lifetime_value,
       NTILE(4) OVER (ORDER BY lifetime_value DESC) AS quartile
FROM   user_features;

Rows are split into 4 buckets; each row gets 1, 2, 3, or 4. Buckets differ in size by at most 1 row. Common ML feature: “is this user a top decile spender?” → NTILE(10) … = 1.

Filtering on the rank — the WHERE trap

You can’t filter directly:

-- ERROR: window functions are not allowed in WHERE
SELECT * FROM orders
WHERE  ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY placed DESC) = 1;

Window functions run after WHERE. Wrap in a subquery / CTE and filter the outer query:

SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY placed DESC) AS rn
    FROM   orders
) t
WHERE  rn = 1;

In Postgres there’s a faster shortcut for “first per group”: SELECT DISTINCT ON (user_id) * FROM orders ORDER BY user_id, placed DESC; — Postgres-only, but extremely useful.

ROW_NUMBER for stable deduplication

When the same logical row appears multiple times in raw data (re-ingested, retries, late events), ROW_NUMBER is the deduper:

WITH ranked AS (
    SELECT *,
           ROW_NUMBER() OVER (
               PARTITION BY business_key
               ORDER BY ingested_at DESC, source_priority
           ) AS rn
    FROM   staging_events
)
SELECT * FROM ranked WHERE rn = 1;

The ORDER BY encodes the dedup priority: latest event wins; if same timestamp, highest-priority source wins. Deterministic, transparent, review-able.

Common pitfalls

• Using RANK for top-K when you wanted ROW_NUMBER. “Top 3” with RANK may return 4 rows if two tie for 3rd. Almost never what you want for pagination.
• No tiebreaker. ROW_NUMBER with non-unique ordering keys gives a different answer between runs. Add a tiebreaker on a unique column.
• Filtering on the rank in WHERE. Doesn’t work. Wrap in a subquery.
• PARTITION BY mismatch with GROUP BY. Common when refactoring from group-by aggregates to window functions; the partitioning columns must include everything that varies across the rows you want grouped.
• NTILE over very small partitions. With 3 rows in a partition, NTILE(10) gives buckets 1, 2, 3 — meaningless. Sanity-check partition sizes.

Production patterns for ML

1. Most-recent label per training event. When labels arrive late (e.g., 7-day return label), pick the latest known label as of the training cutoff:

WITH labeled AS (
    SELECT e.*, l.label,
           ROW_NUMBER() OVER (
               PARTITION BY e.event_id
               ORDER BY l.ts DESC
           ) AS rn
    FROM   events e
    LEFT   JOIN labels l
           ON l.event_id = e.event_id AND l.ts <= e.event_ts + INTERVAL '7 days'
)
SELECT * FROM labeled WHERE rn = 1;

2. Quartile features. Bucket users into spend quartiles for cohort analysis or for using as a categorical feature:

SELECT user_id, lifetime_spend,
       NTILE(4) OVER (ORDER BY lifetime_spend) AS spend_quartile
FROM   user_features;

Then spend_quartile is a clean 4-level categorical the model can learn from, robust to outliers in lifetime_spend.

Resources

• PostgreSQL — window function reference — postgresql.org/docs
• PostgreSQL — window functions tutorial — postgresql.org/docs
• Mode Analytics — window functions — mode.com/sql-tutorial
• PostgreSQL documentation — postgresql.org/docs