Top-N per Group — The RANK / ROW_NUMBER Trap

The single most-asked SQL interview question — with the three subtle wrong-answer traps. FIND_VIDEO: search 'SQL top N per group ROW_NUMBER RANK' — recommended channel: LearnSQL. Aim for 10 min or under.

18 minutesVideo LessonPDF notes

Watch Top-N per Group — The RANK / ROW_NUMBER Trap on YouTube

🎯 Free Guest Mode: You are learning for free. Sign in to save your completion progress and quiz answers.

Loading progress...

Key moments

Ranking Functions Intro — Explains conceptual differences between RANK, DENSE_RANK, and ROW_NUMBER.
Evaluating Gap Behavior in RANK — Demonstrates how duplicate gross revenue values cause rank skipping in SQL Server.
Applying DENSE_RANK and ROW_NUMBER — Compares contiguous dense ranking and distinct row numbering on identical dataset lines.
Ranking on Aggregated Data — Embeds sum aggregations inside window ranking clauses alongside GROUP BY operations.
Partitioned Ranking by Geography — Joins geography dimensions and partitions city gross revenue rankings within each state.
Multi-table Brand Ranking Slices — Joins the item dimension to analyze brand performance partitions across state markets.

PDF notes

Bottom line: You will master the behavioral differences between RANK(), DENSE_RANK(), and ROW_NUMBER() when solving Top-N analytical queries. You will learn how to partition and order window calculations across grouped dimensions without falling into tie-breaking pitfalls.

This lesson on Top-N per Group — The RANK / ROW_NUMBER Trap is hands-on and example-driven. You will master the behavioral differences between RANK(), DENSE_RANK(), and ROW_NUMBER() when solving Top-N analytical queries. You will learn how to partition and order window calculations across grouped dimensions without falling into tie-breaking pitfalls.

What You'll Be Able To Do

Differentiate rank calculation mechanics when handling duplicate metric values.
Write window expressions using PARTITION BY and ORDER BY to rank metrics within sub-categories.
Combine aggregate functions with window ranking functions in grouped queries.
Implement tie-breakers to ensure deterministic ranking outputs in production queries.

Detailed Concept Walkthrough

1. Ranking Function Tie Behaviors

SQL provides three distinct window functions to assign sequence numbers to ordered rows, each resolving ties differently. The choice depends on whether ties should create gaps, stay contiguous, or force arbitrary unique row numbers.

RANK Mechanism: Assigns identical rank values to identical sort keys, but skips subsequent rank numbers by adding the count of duplicate rows (e.g., ties at rank 1 across 82 rows cause the next value to jump to 83).
DENSE_RANK Mechanism: Assigns identical rank values to identical sort keys while keeping numbers strictly consecutive, meaning rank 2 always immediately follows rank 1 regardless of how many rows tied at rank 1.
ROW_NUMBER Mechanism: Generates a strictly unique sequential integer for each row regardless of duplicate values in the ORDER BY clause, breaking ties non-deterministically unless additional unique columns are supplied in the sort clause.

SELECT 
    OrderNumber,
    Quantity * Price AS Gross,
    RANK() OVER (ORDER BY Quantity * Price DESC) AS Rnk,
    DENSE_RANK() OVER (ORDER BY Quantity * Price DESC) AS DenseRnk,
    ROW_NUMBER() OVER (ORDER BY Quantity * Price DESC) AS RowNum
FROM Sales;

Key Takeaway: RANK skips rank values after duplicates, DENSE_RANK stays strictly consecutive, and ROW_NUMBER forces unique incremental integers.

2. Window Functions on Aggregated Data

Window ranking functions can evaluate aggregate expressions directly inside their OVER clause when grouping data with GROUP BY. This eliminates the immediate need for subqueries or CTEs when ranking pre-aggregated group metrics.

Execution Flow: The query engine first groups and aggregates data via the GROUP BY clause before executing the OVER(ORDER BY AGG_FUNC()) window operation on the grouped result set.
Syntax Rule: The metric used in the window ORDER BY must match the aggregate expression in the SELECT list (e.g., SUM(Quantity * Price)), not the raw column name.
Tie-Breaking Nuance: To prevent non-deterministic sorting or arbitrary ROW_NUMBER assignment, add secondary unique expressions (like OrderNumber) into the window ORDER BY clause.

SELECT 
    OrderNumber,
    SUM(Quantity * Price) AS Gross,
    RANK() OVER (ORDER BY SUM(Quantity * Price) DESC, OrderNumber ASC) AS DeterministicRank
FROM Sales
GROUP BY OrderNumber;

Key Takeaway: Place the identical aggregate expression inside the window ORDER BY clause when ranking aggregated dimension groups.

3. Partitioned Ranking Across Dimensions

The PARTITION BY clause resets ranking counters independently for each subset or category boundary. This enables Top-N analysis per group, such as ranking cities within each state or brands within each market.

Mechanism: PARTITION BY divides the intermediate result set into independent partitions where the ranking counter restarts at 1 for each distinct partition key.
Multidimensional Slicing: Swapping the partition and order keys allows contrasting perspectives, such as ranking top brands per state versus ranking top revenue states per brand.
Join Mechanics: Window ranking functions seamlessly span multiple joined relational tables, calculating rank metrics after dimension joins (e.g., Sales, Geography, Item) and group aggregations are resolved.

SELECT 
    g.State,
    g.City,
    SUM(s.Quantity * s.Price) AS Gross,
    RANK() OVER (
        PARTITION BY g.State 
        ORDER BY SUM(s.Quantity * s.Price) DESC
    ) AS CityRankInState
FROM Sales s
INNER JOIN Geography g ON s.CityID = g.CityID
GROUP BY g.State, g.City;

Key Takeaway: PARTITION BY resets rank numbering per category, creating localized Top-N calculations across dimension boundaries.

Topics Covered in Top-N per Group — The RANK / ROW_NUMBER Trap

Ranking Functions Intro (0:00 - 1:05) — Explains conceptual differences between RANK, DENSE_RANK, and ROW_NUMBER.
Evaluating Gap Behavior in RANK (1:06 - 2:35) — Demonstrates how duplicate gross revenue values cause rank skipping in SQL Server.
Applying DENSE_RANK and ROW_NUMBER (2:36 - 3:40) — Compares contiguous dense ranking and distinct row numbering on identical dataset lines.
Ranking on Aggregated Data (3:41 - 5:30) — Embeds sum aggregations inside window ranking clauses alongside GROUP BY operations.
Partitioned Ranking by Geography (5:31 - 7:15) — Joins geography dimensions and partitions city gross revenue rankings within each state.
Multi-table Brand Ranking Slices (7:16 - 8:30) — Joins the item dimension to analyze brand performance partitions across state markets.

SQL Cheat Sheet

RANK() OVER (ORDER BY col DESC) — Ranks rows with gaps after ties
```
RANK() OVER (ORDER BY Quantity * Price DESC)
```
DENSE_RANK() OVER (ORDER BY col DESC) — Ranks rows consecutively without gaps after ties
```
DENSE_RANK() OVER (ORDER BY Quantity * Price DESC)
```
ROW_NUMBER() OVER (ORDER BY col DESC) — Assigns unique consecutive numbers per row
```
ROW_NUMBER() OVER (ORDER BY Quantity * Price DESC)
```
OVER (PARTITION BY grp ORDER BY agg DESC) — Ranks aggregated records within each partition group
```
DENSE_RANK() OVER (PARTITION BY g.State ORDER BY SUM(s.Quantity * s.Price) DESC)
```

Comparison Table

Function	Handling of Duplicate Values	Next Rank After 2 Duplicate 1s
RANK()	Assigns same rank, skips numbers	3
DENSE_RANK()	Assigns same rank, no skip	2
ROW_NUMBER()	Assigns unique consecutive row numbers	2 (first row assigned 1, second 2)

Common Pitfalls

Mistake: Using ROW_NUMBER without secondary unique sort keys for ties. Avoid: Add a deterministic tie-breaker column to the window ORDER BY.
Mistake: Expecting RANK() to produce contiguous integers across duplicate rows. Avoid: Use DENSE_RANK() when consecutive rank numbers without gaps are required.
Mistake: Referencing column aliases inside the OVER() clause in the same SELECT block. Avoid: Repeat the exact aggregation expression inside OVER(ORDER BY ...).

FAQs

Why does RANK() jump from 1 to 83 in duplicate line items? Because 82 rows share the first rank position. RANK() calculates the next rank as the previous rank plus the count of tied records.
Can window ranking functions accept column arguments inside their parentheses? No. Functions like RANK(), DENSE_RANK(), and ROW_NUMBER() do not take arguments; ordering expressions are specified inside OVER(ORDER BY ...).
What happens if PARTITION BY is omitted from a ranking query? The entire result set is treated as a single partition, ranking all rows globally across the whole table.

Common pitfalls

Mistake: Using ROW_NUMBER without secondary unique sort keys for ties. Avoid: Add a deterministic tie-breaker column to the window ORDER BY.
Mistake: Expecting RANK() to produce contiguous integers across duplicate rows. Avoid: Use DENSE_RANK() when consecutive rank numbers without gaps are required.
Mistake: Referencing column aliases inside the OVER() clause in the same SELECT block. Avoid: Repeat the exact aggregation expression inside OVER(ORDER BY ...).

Hello and welcome to another episode of Learn SQL, and we are learning windows function. And we will continue to learn the windows function. And in today's video, we are going to learn RANK, DENSE_RANK, and ROW_NUMBER, and how they are going to behave. So in case of rank, what happens is basically, let's say, I have certain values and I want to rank them, let's say, by quantity. So I will rank them highest as 1, or lowest as 1. Both is possible based on ascending and descending. And it will keep on giving run the rank. Now what happens in such cases, there could be chances the rank the numbers is repeating. So the quantity number might repeat. Now, in case of rank, what happens if the number repeats, let's say, 1 repeat two times, then there would be a 3. It just will skip. In case of dense rank, if the 1 repeat two times, there will still be a 2, so 1 1 2. Means though there would be more than one people at a particular rank, but still, the next rank would come. In case of row number, it try to assign a continuous number to the rows. So we are going to go What we are going to do is, we are going to go to the uh SQL Server Management Studio and start our journey. So, those of you are not following the series from beginning, make sure that you look at the some of the initial video. You will be able to know how we have created this database, how we have installed these things. So, I've already started my database service on my machine and I already opened SQL Server Management Studio. I'm going to right-click here and going to say new query. Now, first thing which I'm going to do is I'm going to say select let's say star from sales. That's my table. Now, you can see I have rows and each rows is a unique order number or I can sum of that quantity at the order number. And the what way I want the reason I wanted to do is the order number is a place where I have lot of data repetition, because beyond that, I would not be having the data repetition. So, let me do quantity star price at this level. I don't need to sum it up. Let's first of all look at this one. And then we will try to also show you the aggregate. Okay. So, this is the number. And just to be uh So, what I can do here is basically let me say order by 2 and desc. Fine. So, you can see the quantities are repeating. I'm going to remove this. So, this is my simple query. Now, I want a ranking here. So, how would I do that? I'll add another column. I'll say rank, function close, over. Now, we need to give order by. And in the order by, let me give this expression. And let me call it desc. Fine. So, what I'm saying here is And let me call it as rank. Okay? So, what I'm saying, order by the quantity into price as gross and rank function, no argument inside that one, over, because if you remember, last time we learned sum, we used to give argument. Here we are not giving any argument. Over, order by we are giving. Right now, we are not talking about partition by. So, we are giving order by and then rank, and then we are saying sum of something. So, now you can see it's automatically uh The database may automatically prefer to sort it on the rank column once you have the these kind of columns. So, you can see all of them are coming 1 1 1 1 1. And let's see just after 1, you are not seeing 2. You are directly seeing 83. And the reason why I'm doing it at a line level, this this is the same reason, because I wanted you to, you know, show that, you know, how the rank works basically. This is what we call sometimes skip rank also. It is going to skip the numbers which are repeated. So, you can think that, you know, the rank 1 is repeating 82 times. So, look at these row numbers which are coming, and you will see here that, you know, this is repeating 82 times. So, we're get the next rank is 83. So, now how does dense rank is going to make a difference? So, let's look at the dense rank. So, what we are going to do here is basically again we are going to copy paste this, and we're going to call it, let's say, drank here. Let's rename that. But the function we are going to use is dense_ rank. Let's see. Let's see what is the function name. So, the function name is dense_rank. Okay, we got it correct. Now. So, now you can see again the 1 1 1 1 1 is repeating. You will say what is the difference? The difference here difference is here in the 83rd line. Now, the next rank is 2. It is not 1 in It is not 83 in this case. Why this is happening? Because in the dense rank, even if a rank repeats, the next next rank need to be the next logical number. If it is 1, it need to be 2. If it is 2, it need to be 3. So, it is consecutive numbers which are going to come out even if there is, you know, a repetition in the rank. So, even there are two people who are holding the top position, still there would be a second ranker. But in case of normal rank, what happens? If there are two point people holding the top position, then there would be third position. There would not be a second position because there are two people on the first rank. So, hope you understand the difference. Now, let's look at the third function and then we will have to do lot of experiments. Um Now, we will go to row_ number. Most of the database wherever these are supported, you will see almost similar kind of syntax. Now, here what you see the thing here is you are getting the consecutive numbers, the row numbers. Now, here you might be asking what is the benefit. Now, look at things when you group by the data, then you might find the importance of having the continuous number. So, though the And And thing is that, you know, the highest one is still getting the first row number. But if you are the second in that one, then if that is repeating, you're going to get a unique number. And in that order, it is coming. So, that is the importance. Now, this row number which we are seeing here, it could have come in any order. It it doesn't mean that. And we are also not getting that as an output. So, this is the row number we which we can get for based on our output. So, we can get like this row number and that could be really helpful. Now, do we are going to analyze the data now like this? No. Most of the time, what happens is basically, we are going to analyze this data by, you know, grouping it. So, what would be my requirement would be, let's say, I will say sum of gross, quantity into gross. And then, I need to have an order by also. Group by also. What is my group by? Group by my order number. Now, in such case, if I simply do it, it might give error. So, what we are going to do here is, we are going to put this also into this one here, order by. So, in the order by, we'll say put this and then try out. Can we do it without going to the outer query? Let's try that out. So, similar kind of output now we are getting. What is happening in this case is basically that we are able to get the same rank by group by. Now, the entire box has opened. We have, you know, we can do multiple combinations and we can do these things. So, why don't we try out with the let's say, state, city, joining multiple tables, and try that out? So, right now at the order number, we know that the order number was unique. And what we can do is basically we can run both of these queries together. That's the beauty of SQL Server Management Studio. It will be able to show us both the things together. And you can see Uh now, here you might be seeing that there's a difference. Now, because they are having the same number, it doesn't mean that it's going to give the same uh uh order number again and again, because there's no role order number is playing here. So, you have to remember that, you know, order number is not playing a role. And because of that, it's may not give us the same rank number. Okay? So, can we give order number also here? So, in the only in the one, I'm trying. Let me see. So, now you see and the order number basically, we have to say descending and this we have to say ascending. We are complicating the stuff now. Now, you can see Now, what has happened, you are started getting the unique ranks. Why you are getting the unique ranks just like this one? The reason for the first order number when it is same, the the sum of quantity into this one was same. The order number is actually creating a unique combination. So, the combination is now unique and you are seeing the rank is almost unique. And in our case, the order numbers are not repeating. We have grouped by. So, order number are not going to repeat. So, that is why what happens now, the rank has almost become like a run row number. Let's call it row. So, rank has become row number. Change here also. For your reference, I've changed that, both the places. Okay. So, now let's do one thing. Let's Now we know that we can do these kind of things. Let's copy paste this. We don't need this order number now. And this one also. But we would be needing this. All these things we would be needing. But now let us do one thing. Let us do small change. Let's join it. So, we say inner join. And those of you who are following the series, you know that most of our tables are having this kind of join. Let me first join with the state, which is geography table. And how do we join with geography table? Inner join with geography, let's call sales as s, alias s, geography as g, and we say on s.cityid equals to g. city id. First thing I'm going to do is g.state and g.city. And we will say group by also this one, isn't it? Fine. So, we are saying select g geography state, geography city, sum quantity into price as gross, rank over order by sum of quantity into price as descending as rank, dense rank over the same thing as dense rank, and row number over the same thing as a row number. And then we are saying group by. Now, you might see few things similar here, because at city level, it may happen city state level that our data is not going to be same. So, what you will see most of the time these things are behaving in the similar fashion, because most of our numbers are not same. They are unique numbers at the state city level. So, now what you are seeing here is, I got my top city, Colorado, which is having the highest gross, means quantity into price. And then I the ranked. Now, this is because I have unique city, I could have done this. This would give me the same number as city. Okay? So, now what is happening right now is, we are grouping the data and at that data, we are getting this number. Now, can I do a little bit differently? Can I get the ranking inside the state? Can I get the city ranking inside the state? So, I would What I would like is I would like to rank my cities based on this gross inside the state. So, then we know we have something known as partition by. So, let us try that. So, to give the partition by what we need is basically uh uh that, you know, let's start with the giving here partition by. And let's use g.state. So, we give partition by g.state and order by. Now, what I'm going to do, I'm going to right now keep it here only uh fill this one only. And then we will change it everywhere. So, now you see 1 1 1 1 1. I'll tell you the reason, because what is happened, now we need order by basically. So, order by state comma 1, 2, I think the third one. And third one we need ascending only. So, ascending we don't need to give. Fine. So, now you can see that we are getting the ranks. So, we got rank 1 here. And then we need got Arizona, 7, 6, 5, 4. Third one was gross basically. So, let's give fourth one. See, we can give number as well as the name. Now, this is the first one, Arizona. First one is this, second one is this, third one is this, fourth one is this, fifth one is this, sixth one is this, seventh one is this. Okay? So, now this is the this is happening inside the state. So, why don't we go ahead and repeat these things for the other ones? Let's add partition by. So, we will say over partition by, then order by, over partition by, then order by. Okay? Now, let's run this query. And now even if I remove order by, we should get something better. Let's see what happens. Do we need an order by? Otherwise, we'll add order by. Okay. So, now because everything is already done, so it's giving us better. And now you will see all the these three values are coming there. You will might see only a one or two exception here where the values might repeat. But because our values at the state and the city level are almost unique, you don't see anything like that. So, now what is happening, you got a rank inside the state. So, the entire ranking is happening inside the state. So, what you call typically, you call that give me the city rank by gross inside the state. See, don't get confused here. The city name is not ranked. City is ranked by gross inside the state. So, that's the problem statement. Okay? So, we got it now. Now, we can do it across table also. Right Right now, we are taking city state, but we might have it at instead of this one, let's take one more example where what we what is going to happen? Now, we can do one more join. Again, you know that our joins are pretty, you know, straightforward. They all of them can be inner join. So, we do inner join with item, which is we going to call as i, on i. i.itemid equals to g.item id. Okay? Uh it's not g, it's s.itemid. So, i.itemid equal to s.itemid, we got it. And from item, we are going to bring in brand here. We don't want need city. So, i.brand and we'll replace i.brand here also. Now, rest of the code is going to remain same here, because here we are still going to do the order by state. Now, we might line want to see inside a brand how these things are happening. That is also possible. But first let's look how each brand is behaving inside a particular state. And now you can see that, you know, in Alaska the brand wise, this is the biggest number in Alaska. And this is ranked 1, then 2, 3, 4, 5, 6, 7, 8, and so on. We have 13 brands, so definitely most of the states would have the 13 brands and we'll get 13 ranks. Now, there could be some states which are not having. Now, this is the one way to do that. Now, we may like to do this thing One more thing we may like to do here is basically how each how each state is ranking inside the brand. So, not only this, now I'm a brand, you know, I'm one person who is looking at the brands. And I would like how my brands are doing in the state, which state is topped for this brand. Let's say I sell a particular brand, let's say Apple. How What state is topping in the Apple? Okay? So, I might have that kind of uh, you know, wish list. So, in this one, we change it a little bit and let's see now what happens here is basically Now, you can see the good thing here is that the brand 1 is coming. And in the inside the brand 1, we know the California is at the rank 1. Now, let's go inside the brand 2. Almost 100 records almost whatever states we have, 30, 34, 35 states are going to come. Again, you can see based on this gross, in the California is again number 1. In the brand 10 in brand 11 also, California is number 1. So, now you know brand wise which state is number 1. For each brand, which state is number 1. Previously, inside the state, we know which brand is number 1. Now, for each brand, you know which state is number 1. So, different kind of analysis can be done. And these kind of analysis are really useful for taking the decision. Analytical decision-making, these functions, sum over, partition by, group by, all these are really important. And we will have more and more complex query using these functions as we move forward. So, go ahead and try these things out, and do let me know what else you want me to cover in this particular series. Thanks for watching this video. Thank you. Keep watching. Keep asking questions in comments. Subscribe to my channel and press the bell icon so that you can get notification for new videos. Thank you.

Frequently asked questions

Why does RANK() jump from 1 to 83 in duplicate line items?

Because 82 rows share the first rank position. RANK() calculates the next rank as the previous rank plus the count of tied records.

Can window ranking functions accept column arguments inside their parentheses?

No. Functions like RANK(), DENSE_RANK(), and ROW_NUMBER() do not take arguments; ordering expressions are specified inside OVER(ORDER BY ...).

What happens if PARTITION BY is omitted from a ranking query?

The entire result set is treated as a single partition, ranking all rows globally across the whole table.

How was this lesson?

Your feedback helps us refine explanations and catch bugs.