The RANK, the DENSE_RANK and the ROW_NUMBER look almost identical, and they behave differently only when values tie. The RANK leaves gaps after a tie, the DENSE_RANK does not, and the ROW_NUMBER breaks the tie in an arbitrary way so that every row gets its own number. Choose wrongly and you either skip ranks or number rows that you thought were tied, and the two mistakes produce very different business answers.
The previous lesson in this module covered "What Do Window Functions Add to Aggregates?", and the next lesson in this module is about "How Do Frames Compute Running Totals and Moving Averages?".
How do RANK, DENSE_RANK and ROW_NUMBER differ?
All three number the rows in order, and all three read the same ORDER BY expression. The difference appears only when two rows have the same ranking value.
Here are some sales from the sample. One of them is larger than the others, and three of them are exactly equal. That group of three is where the three functions part company.
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 5Those three sales are tied at 585.
The RANK gives all three of them the number 2, and then it skips the 3 and the 4, so the next sale down is ranked the 5.
The DENSE_RANK gives all three of them the number 2, and the next sale down is ranked the 3, because it counts the distinct values rather than the rows.
The ROW_NUMBER gives every row a distinct number, and it breaks the tie by whatever order the engine happens to produce. That is the reason the last two columns disagree. Never rely on that order, because it can change when the data or the query plan changes.
Which one should you use?
RANKfor the competition rankings: first, second, third, and then the fifth. This is the Olympic convention, and it is what a "top three" filter should use. The conditionWHERE rnk <= 3can legitimately return more than three rows when there is a tie for the third. It answers the question "how many are ahead of this one?".DENSE_RANKwhen every place should be usable as a threshold, which means the top three of the distinct values. It answers the question "how many distinct values are ahead of this one?".ROW_NUMBERwhen you need a guaranteed unique number. That is for picking exactly three rows, for numbering a list for the display, or for de-duplicating with a filter onrow_no = 1. It answers the question "which row is this, according to the engine?".
The tie between those first two is a business decision and not a technical one. If a customer is ranked the third and another one is also ranked the third, do you want a fourth place to exist? The competition rankings say no, and the dense rankings say yes. Pick the one that matches the rule your business already uses, and write that rule down next to the query.
How do you rank within a group?
The PARTITION BY does the grouping, and the ranking functions work exactly as they do at the top level.
Each rep's sales are ranked against that rep's own best, and the query asks for two reps.
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 5Each rep's numbering starts again at 1, and the two reps have completely different orderings. Ada's best sale is a monitor arm worth 1008.90, and Carla's is a task chair at 769.50.
The ranking inside the partition is what makes this a per-rep leaderboard and not a company-wide one. Without the partition, Carla's best sale would sit somewhere in the middle of the company list.
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. You rank first, and then you filter. The CTE computes the rank, and the outer query keeps the rows where the rank is 1.
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.25There is one row for each region, and it is the best rep in that region. The East rep, the North rep, the South rep, and the West rep each get a line.
Notice the RANK and not the ROW_NUMBER. If the two reps in a region were level on the revenue, both of them would be shown, and for "who is our best rep" that is the right behaviour. Switching to the ROW_NUMBER would silently hide one of them. The same argument applies to a tie in a product ranking, where you probably want to show both lines.
The same shape ranks the products across the whole company, with no partition at all.
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 5The Task Chair and the Filing Cabinet are within ten pounds of each other, and the ranking makes that closeness visible in a way that a plain sorted list does not. The ranks tell you that there is nothing between the two of them.
Quick reference
RANKnumbers the rows by position and leaves gaps after a tie, andDENSE_RANKdoes not leave the gaps.ROW_NUMBERalways produces distinct numbers, so the order inside a tie is arbitrary.- Use
RANKfor the competition places,DENSE_RANKfor the distinct-value thresholds, andROW_NUMBERfor exactly N rows. PARTITION BYrestarts the numbering for each group.- A window function cannot appear in the
WHERE, so you rank in a subquery or a CTE, and then you filter on the rank.
Try it yourself: ranking drill. Write a query that returns the top two sales for each rep, and then decide whether to use the RANK or the ROW_NUMBER. Explain in one sentence what would happen if two of a rep's sales were exactly equal.