RANK, DENSE_RANK and ROW_NUMBER look almost identical and behave differently only when values tie. RANK leaves gaps after a tie, DENSE_RANK does not, and ROW_NUMBER breaks the tie arbitrarily so that every row gets its own number. Choosing wrongly means either skipping ranks or numbering rows you thought were tied — and the two mistakes produce very different business answers.
How do RANK, DENSE_RANK and ROW_NUMBER differ?
All three number rows in order. The difference appears only when two rows have the same ranking value. Here are three sales, one of them larger than the other two, which are exactly equal:
SELECT s.sale_id, ROUND(s.quantity * s.unit_price * (1 - s.discount), 2) AS revenue,
RANK() OVER (ORDER BY s.quantity * s.unit_price * (1 - s.discount) DESC) AS rnk,
DENSE_RANK() OVER (ORDER BY s.quantity * s.unit_price * (1 - s.discount) DESC) AS dense,
ROW_NUMBER() OVER (ORDER BY s.quantity * s.unit_price * (1 - s.discount) DESC) AS row_no
FROM sales s
WHERE s.sale_id IN (4, 13, 23, 32, 1)
ORDER BY revenue DESC;
sale_id revenue rnk dense row_no
------- ------- --- ----- ------
4 769.5 1 1 1
13 585.0 2 2 2
23 585.0 2 2 3
32 585.0 2 2 4
1 320.0 5 3 5
Three sales are tied at 585. RANK gives all three the number 2 — and then skips 3 and 4, so the next sale down is ranked 5. DENSE_RANK gives all three the number 2 and the next sale down is ranked 3, because it counts distinct values rather than rows. ROW_NUMBER gives every row a distinct number, breaking the tie by whatever order the engine happens to produce — which is why the last two columns disagree.
Which one should you use?
- RANK for competition rankings: first, second, third, then fifth. This is the Olympic convention, and it is what a "top three" filter should use —
WHERE rnk <= 3can legitimately return more than three rows when there is a tie for third. - DENSE_RANK when every place should be usable as a threshold: the top three distinct values.
- ROW_NUMBER when you need a guaranteed unique number — picking exactly three rows, numbering a list for display, or de-duplicating with a filter on row_no = 1.
The tie between those first two is a business decision, not a technical one. If a customer is ranked third and another is also ranked third, do you want a fourth place to exist? Competition rankings say no; dense rankings say yes.
How do you rank within a group?
PARTITION BY does the grouping, and the ranking functions work exactly as they do at the top level. Each rep's sales ranked against that rep's own best:
SELECT r.rep_name, p.product_name,
ROUND(s.quantity * s.unit_price * (1 - s.discount), 2) AS revenue,
RANK() OVER (PARTITION BY r.rep_id ORDER BY s.quantity * s.unit_price * (1 - s.discount) DESC) AS rnk,
DENSE_RANK() OVER (PARTITION BY r.rep_id ORDER BY s.quantity * s.unit_price * (1 - s.discount) DESC) AS dense,
ROW_NUMBER() OVER (PARTITION BY r.rep_id ORDER BY s.quantity * s.unit_price * (1 - s.discount) DESC) AS row_no
FROM sales s
JOIN reps r ON r.rep_id = s.rep_id
JOIN product_lines p ON p.product_id = s.product_id
WHERE r.rep_id IN (1, 3)
ORDER BY r.rep_name, revenue DESC;
rep_name product_name revenue rnk dense row_no
------------- -------------- ------- --- ----- ------
Ada Whitfield Monitor Arm 1008.9 1 1 1
Ada Whitfield Filing Cabinet 585.0 2 2 2
Ada Whitfield Bookshelf 480.0 3 3 3
Ada Whitfield Desk Lamp 320.0 4 4 4
Ada Whitfield Task Chair 285.0 5 5 5
Ada Whitfield Rug 240.0 6 6 6
Carla Nunes Task Chair 769.5 1 1 1
Carla Nunes Filing Cabinet 585.0 2 2 2
Carla Nunes Rug 525.0 3 3 3
Carla Nunes Desk Lamp 384.0 4 4 4
Carla Nunes Bookshelf 240.0 5 5 5
Each rep's numbering starts again at one, and the two reps have completely different orderings. Ada's best sale is a monitor arm worth 1008.90; Carla's is a task chair at 769.50. Ranking within the partition is what makes this a per-rep leaderboard rather than a company-wide one.
How do you find the top row in each group?
That is the classic use of the ranking functions, and it needs a subquery — because a window function cannot be used directly in a WHERE clause. Rank first, then filter:
WITH ranked AS (
SELECT rg.region_name, r.rep_name,
ROUND(SUM(s.quantity * s.unit_price * (1 - s.discount)), 2) AS revenue,
RANK() OVER (PARTITION BY rg.region_id ORDER BY SUM(s.quantity * s.unit_price * (1 - s.discount)) DESC) AS rnk
FROM sales s
JOIN reps r ON r.rep_id = s.rep_id
JOIN regions rg ON rg.region_id = r.region_id
GROUP BY rg.region_id, rg.region_name, r.rep_id, r.rep_name
)
SELECT region_name, rep_name, revenue FROM ranked WHERE rnk = 1 ORDER BY region_name;
region_name rep_name revenue
----------- ------------- --------
East Greta Vogel 2955.0
North Bruno Kessler 3273.5
South Carla Nunes 2503.5
West Elif Demir 2627.25
One row per region, the best rep in each. Note the RANK rather than ROW_NUMBER: if two reps in a region were level on revenue, both would be shown — and for "who is our best rep" that is the right behaviour. Switching to ROW_NUMBER would silently hide one of them.
The same shape ranks products across the whole company:
SELECT p.product_name, ROUND(SUM(s.quantity * s.unit_price * (1 - s.discount)), 2) AS revenue,
RANK() OVER (ORDER BY SUM(s.quantity * s.unit_price * (1 - s.discount)) DESC) AS rnk
FROM sales s JOIN product_lines p ON p.product_id = s.product_id
GROUP BY p.product_id, p.product_name
ORDER BY revenue DESC
LIMIT 5;
product_name revenue rnk
-------------- ------- ---
Task Chair 3519.75 1
Filing Cabinet 3510.0 2
Desk Lamp 2496.0 3
Standing Desk 2037.0 4
Rug 2025.0 5
Task Chair and Filing Cabinet are within ten pounds of each other, and the ranking makes that closeness visible in a way a plain sorted list does not.
Quick reference
- RANK numbers rows by position and leaves gaps after a tie; DENSE_RANK does not leave gaps.
- ROW_NUMBER always produces distinct numbers, so the order within a tie is arbitrary.
- Use RANK for competition places, DENSE_RANK for distinct-value thresholds, ROW_NUMBER for exactly N rows.
- PARTITION BY restarts the numbering for each group.
- A window function cannot appear in WHERE — rank in a subquery or CTE, then filter on the rank.
Ranking drill: write a query that returns the top two sales for each rep — and then decide whether to use RANK or ROW_NUMBER, explaining in one sentence what would happen if two of a rep's sales were exactly equal.