RANK vs DENSE_RANK vs ROW_NUMBER in SQL, explained with ties

RANK vs DENSE_RANK vs ROW_NUMBER only matters when rows tie. ROW_NUMBER() gives every row its own number, 1, 2, 3, 4, even for equal values. RANK() gives tied rows the same number and then skips, 1, 1, 3. DENSE_RANK() gives tied rows the same number and does not skip, 1, 1, 2. Use ROW_NUMBER for exactly one row, DENSE_RANK for the Nth distinct value, and RANK for a leaderboard.

What is the difference between RANK, DENSE_RANK and ROW_NUMBER?

RANK, DENSE_RANK and ROW_NUMBER all number rows in the order of the window’s ORDER BY, and they differ only on ties. Ava and Ben both scored 95, and Cara and Dan both scored 90, so the three columns split apart from the second row on:

WITH scores(name, score) AS (
  VALUES ('Ava', 95), ('Ben', 95), ('Cara', 90), ('Dan', 90), ('Eli', 85), ('Fay', 80)
)
SELECT
  name,
  score,
  ROW_NUMBER() OVER (ORDER BY score DESC, name) AS row_num,
  RANK() OVER (ORDER BY score DESC) AS rnk,
  DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rnk
FROM scores
ORDER BY score DESC, name;

The aliases are rnk and dense_rnk on purpose. MySQL 8.0.2 and later reserve RANK, DENSE_RANK and ROW_NUMBER as keywords, so AS rank is a syntax error there unless you quote it. PostgreSQL accepts AS rank.

What is the difference between RANK and DENSE_RANK?

RANK counts rows and DENSE_RANK counts values. A row’s RANK() is one more than the number of rows ahead of it, so Eli gets 5 because four people beat him. A row’s DENSE_RANK() is one more than the number of distinct values ahead of it, so Eli gets 3 because two scores beat his. RANK leaves a gap after every tie, the size of the tie minus one; DENSE_RANK never leaves a gap.

The gap is what decides a filter. WHERE rnk = 2 returns nothing on the scores above, because no row has rank 2, while WHERE dense_rnk = 2 returns Cara and Dan. That is why the second highest salary is a DENSE_RANK question, as the second highest salary post shows with its own wrong answers.

Why does ROW_NUMBER give tied rows different numbers?

ROW_NUMBER never repeats a number inside a partition, so when two rows tie it has to put one first, and without a tiebreaker the database may pick either one. The result can change between runs, versions or query plans. Add a column that is unique to the end of the window’s ORDER BY, as ORDER BY score DESC, name does above, and the numbering is the same every time.

Add a unique column to the window’s ORDER BY whenever you use ROW_NUMBER.

RANK and DENSE_RANK need no tiebreaker, because tied rows get the same number whichever comes first.

Which one returns the top 3?

Each function answers a different “top 3”. ROW_NUMBER() <= 3 returns exactly three rows and cuts a tie in half. RANK() <= 3 returns everyone who finished in the first three places, which can be more than three rows. DENSE_RANK() <= 3 returns everyone holding one of the three highest values, which can be many more:

WITH scores(name, score) AS (
  VALUES ('Ava', 95), ('Ben', 95), ('Cara', 90), ('Dan', 90), ('Eli', 85), ('Fay', 80)
),
ranked AS (
  SELECT
    name,
    ROW_NUMBER() OVER (ORDER BY score DESC, name) AS row_num,
    RANK() OVER (ORDER BY score DESC) AS rnk,
    DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rnk
  FROM scores
)
SELECT
  COUNT(*) FILTER (WHERE row_num <= 3) AS row_number_top3,
  COUNT(*) FILTER (WHERE rnk <= 3) AS rank_top3,
  COUNT(*) FILTER (WHERE dense_rnk <= 3) AS dense_rank_top3
FROM ranked;

In an interview, ask which one the question means before you write it. “The three highest paid employees” usually means RANK or ROW_NUMBER, and “the three highest salaries” means DENSE_RANK.

How do RANK, DENSE_RANK and ROW_NUMBER work with PARTITION BY?

PARTITION BY restarts the numbering in every group, and ties are handled the same way inside each group. Each department below starts again at 1, and the two 7000 salaries in Sales share a rank:

WITH employees(dept, name, salary) AS (
  VALUES ('Sales', 'Ava', 7000), ('Sales', 'Ben', 7000), ('Sales', 'Cara', 5000),
         ('Tech', 'Dan', 9000), ('Tech', 'Eli', 8000), ('Tech', 'Fay', 8000)
)
SELECT
  dept,
  name,
  salary,
  ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC, name) AS row_num,
  RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rnk,
  DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS dense_rnk
FROM employees
ORDER BY dept, salary DESC, name;

How do I get exactly one row per group?

Use ROW_NUMBER() = 1 with a tiebreaker. ROW_NUMBER is the only one of the three that can never give two rows the number 1, so the filter always returns one row per group, the top earner in each department here:

WITH employees(dept, name, salary) AS (
  VALUES ('Sales', 'Ava', 7000), ('Sales', 'Ben', 7000), ('Sales', 'Cara', 5000),
         ('Tech', 'Dan', 9000), ('Tech', 'Eli', 8000), ('Tech', 'Fay', 8000)
),
ranked AS (
  SELECT dept, name, salary,
         ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC, name) AS row_num
  FROM employees
)
SELECT dept, name, salary
FROM ranked
WHERE row_num = 1
ORDER BY dept;

Swap in RANK() = 1 and Sales returns both Ava and Ben, which is right when the question asks for every top earner. The filter sits in an outer query because a window function cannot appear in the WHERE of the query that computes it. The same one row per group pattern also fixes the GROUP BY error when you want the whole row, not just the maximum.

Why does RANK() return 1 for every row?

RANK() returns 1 for every row when its OVER () has no ORDER BY. Without an order, every row of the partition ties with every other row, so they all share rank 1, and DENSE_RANK does the same:

WITH scores(name, score) AS (
  VALUES ('Ava', 95), ('Cara', 90), ('Eli', 85)
)
SELECT
  name,
  RANK() OVER () AS rnk,
  DENSE_RANK() OVER () AS dense_rnk
FROM scores
ORDER BY name;

PostgreSQL gives the same answer as the SQL engine on this page, and SQL Server refuses to run a ranking function without an ORDER BY in its OVER clause. Put the column you rank by inside OVER (ORDER BY ...); the query’s own ORDER BY only sorts the output and does not change the numbers.

Which should I use: RANK, DENSE_RANK or ROW_NUMBER?

Pick by what a tie should mean in the answer:

The PostgreSQL window functions reference defines rank as the row number of the first row in its peer group and dense_rank as a count of peer groups. The MySQL window function descriptions cover the same three functions for MySQL 8.4, and the MySQL keywords list marks them reserved. More questions that turn on ties are in the SQL interview practice guide.

Related questions

Top N per group, the second highest salary in each department, and finding duplicate rows with ROW_NUMBER each have answers of their own. The error “window functions are not allowed in WHERE” comes from filtering on one of these functions in the query that computes it, and has its own fix.