SQL ROW_NUMBER vs RANK vs DENSE_RANK

How window functions work

A window function computes a value for each row from a "window" of related rows, without collapsing them like GROUP BY does. OVER (PARTITION BY dept_id ORDER BY salary DESC) means: within each department, ordered by salary from highest.

In Sales, Meera and Arjun both earn 64,000. RANK and DENSE_RANK give them both 1. ROW_NUMBER must still give 1 and 2, so add a tie-breaker (here name) to make that choice predictable.

Which one to use

Use ROW_NUMBER for exactly one row per group (deduplication, latest record). Use DENSE_RANK for "top N distinct values" such as the top three salary levels. Use RANK when you need competition-style ranking, where ties push later ranks down.

The example tables

Every example on this page uses these tables. They are small on purpose, so you can check each result by eye.

Examples

SELECT name, dept_id, salary,
       ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC, name) AS row_num,
       RANK()       OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rnk,
       DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS dense_rnk
FROM employees
WHERE dept_id = 20
ORDER BY salary DESC, name;

SELECT name, salary,
       RANK() OVER (ORDER BY salary DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rnk
FROM employees
ORDER BY salary DESC, name;

WITH ranked AS (
  SELECT name, dept_id, salary,
         ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC, name) AS rn
  FROM employees
  WHERE dept_id IS NOT NULL
)
SELECT dept_id, name, salary
FROM ranked
WHERE rn = 1
ORDER BY dept_id;

Common mistakes

• ROW_NUMBER when ties should count. "Highest-paid employees per department" usually means everyone tied at the top. Use RANK or DENSE_RANK then.
• No tie-breaker. With ties, ROW_NUMBER picks an arbitrary order unless you add a second ORDER BY column.
• Filtering in the same SELECT. You cannot write WHERE rn = 1 next to the window function. Wrap it in a CTE or subquery.

Practise ROW_NUMBER vs RANK vs DENSE_RANK

Interview questions that use this concept, easiest first. Each one has the step-by-step visualizer and answer checking.

1. 1

   Rank Salaries Within Department

   Assign dense rank values to employee compensations per department without skipping ranks for tied salaries.

   Medium✓ Solved
2. 2

   Month-over-Month Revenue Growth

   Compute monthly successful payment volume and its percentage change versus the prior month.

   Medium✓ Solved
3. 3

   Running Total of Daily Sales

   Calculate a cumulative running balance of transaction amounts partitioned by retail store location.

   Medium✓ Solved
4. 4

   Amazon: First Order Details

   Retrieve each customer's very first order, resolving date ties by selecting the lowest order_id.

   Medium✓ Solved
5. 5

   Amazon: Days Between Repeat Orders

   Calculate the average gap in days between consecutive purchases for customers with repeat orders.

   Medium✓ Solved
6. 6

   Amazon: Best-Selling Products per Category

   Identify the top-selling product in each retail category by total units sold, returning all tied leaders using DENSE_RANK.

   Medium✓ Solved

Want a full set? Work through the Window Functions study plan.

Questions people ask

Does MySQL support window functions?

Yes, since MySQL 8.0. PostgreSQL has supported them for much longer. The syntax shown here works in both.

What does PARTITION BY do?

It splits rows into groups and restarts the numbering in each group, while keeping every row in the result.

Related concepts

• SQL LAG and LEAD Functions Explained
• SQL CTE (WITH Clause) Explained with Examples