RANK vs DENSE_RANK vs ROW_NUMBER

ROW_NUMBER — every row gets a unique number

ROW_NUMBER assigns 1, 2, 3, ... to rows in the order specified by the OVER clause. Two rows with the same sort key are treated as different — one of them gets, say, 3 and the other gets 4, but which one is which is implementation-defined unless you break the tie with another column in ORDER BY.

Use it when you genuinely need a unique sequential number per row: paginating a result set, picking exactly one row per group, deduplicating. The non-determinism on ties is fine when you do not care which row wins — and a bug when you do.

A common ROW_NUMBER pattern is "give me the latest record per entity." On ods_appl_status_history, ROW_NUMBER over PARTITION BY applications_id ORDER BY appl_status_date DESC inside a CTE lets you keep WHERE rn = 1 in the outer query and get exactly one current status per application.

-- Rank applicants by high-school GPA; every row gets a unique number
-- (names live on ods_person; the GPA is on the application)
SELECT
  p.first_name,
  p.last_name,
  a.appl_hs_gpa,
  ROW_NUMBER() OVER (ORDER BY a.appl_hs_gpa DESC) AS rn
FROM ods_applications a
JOIN ods_person p ON p.id = a.appl_applicant
WHERE a.appl_hs_gpa IS NOT NULL
ORDER BY rn;

-- Output (notice the tie gets different numbers):
-- Mateo   Munoz     3.98  1
-- Micah   Alonzo    3.97  2
-- Diego   Ávila     3.94  3
-- Raven   Diallo    3.94  4  <- same GPA, different rn
-- Camila  Castillo  3.91  5

RANK — ties tie, gaps follow

RANK assigns the same number to rows that tie on the ORDER BY columns, then skips ahead. If two rows tie for third place, both get rank 3 — and the next row gets rank 5, not 4. This is the rank you would expect on a leaderboard or in standings: gold, silver, silver, bronze does not exist; the next medal after a tied silver is fourth place.

Use it when ties should genuinely share a position and the resulting gaps are meaningful — academic standing, sports rankings, leaderboards. The gap is information: it tells you how many people were tied above you.

A subtle one: RANK only ties rows that match on every column in ORDER BY. If you write ORDER BY appl_hs_gpa DESC, two applicants with the same GPA tie. If you add , last_name as a tiebreaker, they no longer tie — and you are back to ROW_NUMBER-like output.

-- Same applicants, but ties share a rank — and the next rank skips
SELECT
  p.first_name,
  p.last_name,
  a.appl_hs_gpa,
  RANK() OVER (ORDER BY a.appl_hs_gpa DESC) AS rk
FROM ods_applications a
JOIN ods_person p ON p.id = a.appl_applicant
WHERE a.appl_hs_gpa IS NOT NULL
ORDER BY rk;

-- Output:
-- Mateo   Munoz     3.98  1
-- Micah   Alonzo    3.97  2
-- Diego   Ávila     3.94  3
-- Raven   Diallo    3.94  3  <- same GPA, same rank
-- Camila  Castillo  3.91  5  <- skipped 4

DENSE_RANK — ties tie, no gaps

DENSE_RANK is identical to RANK except for the gap behavior. Two rows that tie share a rank, but the next rank is the next consecutive integer — never a skip. If two applicants tie at rank 3, the next applicant is rank 4.

Use it when you care about distinct positions ("how many distinct GPA values are above this applicant?") and the count of rows tied does not matter. Dense ranks are also better for grouping into bins of equal "rank value" rather than "rank order" — for example, "everyone in the top three GPA values" is WHERE dr <= 3, regardless of how many applicants are tied at each value.

In practice, RANK is more common for human-facing leaderboards (because skipped numbers are familiar) and DENSE_RANK is more common for analyst-shaped questions (because you usually want to count distinct values).

-- Ties share a rank, but no gaps appear
SELECT
  p.first_name,
  p.last_name,
  a.appl_hs_gpa,
  DENSE_RANK() OVER (ORDER BY a.appl_hs_gpa DESC) AS dr
FROM ods_applications a
JOIN ods_person p ON p.id = a.appl_applicant
WHERE a.appl_hs_gpa IS NOT NULL
ORDER BY dr;

-- Output:
-- Mateo   Munoz     3.98  1
-- Micah   Alonzo    3.97  2
-- Diego   Ávila     3.94  3
-- Raven   Diallo    3.94  3
-- Camila  Castillo  3.91  4  <- no skip

PARTITION BY — group-aware ranking

Add PARTITION BY inside the OVER clause to reset the ranking for each group. This is what turns these functions from a single global ranking into a useful per-group one: rank each applicant within the program they applied to, find the top earner per department, the latest enrollment per term.

Mechanically: PARTITION BY divides the rows into groups, and the ranking function runs independently inside each group. An applicant ranked 1st in their program might be ranked 10th overall. The function does not see across partitions.

PARTITION BY pairs especially well with ROW_NUMBER for the "one row per group" pattern: rank rows within each group by a sort key, filter for rn = 1 in the outer query, get exactly one row per group. The tiebreaker on last_name below is what makes the answer deterministic when two applicants to the same program share a GPA.

-- Top applicant per program: ROW_NUMBER + PARTITION BY
WITH ranked AS (
  SELECT
    p.first_name,
    p.last_name,
    a.appl_acad_program_title AS program,
    a.appl_hs_gpa,
    ROW_NUMBER() OVER (
      PARTITION BY a.appl_acad_program_title
      ORDER BY a.appl_hs_gpa DESC, p.last_name
    ) AS rn
  FROM ods_applications a
  JOIN ods_person p ON p.id = a.appl_applicant
  WHERE a.appl_hs_gpa IS NOT NULL
    AND a.appl_acad_program_title IS NOT NULL
)
SELECT first_name, last_name, program, appl_hs_gpa
FROM ranked
WHERE rn = 1
ORDER BY program;

-- Wenjing  Suzuki   BA English   3.46
-- Raven    Diallo   BA History   3.94
-- ...one row per program

Picking the right one

Three questions decide it. First: do ties matter? If you need a unique number per row regardless of ties, ROW_NUMBER. If ties should share a position, RANK or DENSE_RANK.

Second: when ties happen, should the next rank skip ahead or not? Skip = RANK. No skip = DENSE_RANK.

Third: ranking globally or per group? Per group means add PARTITION BY — to any of the three. The choice between PARTITION BY and no PARTITION BY is independent of the ranking function; combine them however the question requires.

-- Quick reference, side by side on identical input
SELECT
  appl_hs_gpa,
  ROW_NUMBER() OVER (ORDER BY appl_hs_gpa DESC) AS rn,
  RANK()       OVER (ORDER BY appl_hs_gpa DESC) AS rk,
  DENSE_RANK() OVER (ORDER BY appl_hs_gpa DESC) AS dr
FROM ods_applications
WHERE appl_hs_gpa IS NOT NULL
ORDER BY rn;

-- gpa   rn  rk  dr
-- 3.98   1   1   1
-- 3.97   2   2   2
-- 3.94   3   3   3
-- 3.94   4   3   3
-- 3.91   5   5   4

Practice these on QueryU

Window functions run from the medium tier into the hard tier. Each challenge below uses one on the same dataset, and is graded on its result set — solving them is the fastest way to build comfortable intuition.

Frequently asked questions