Ranking in SQL Server

Have you ever needed a Top 10 list? It may sound like a simple task but not all lists are the same. Consider records where there are ties. Does that mean you need more than 10 records? If two records tie for first does that mean the third record is considered to be ranked second? No matter what type of list you need, SQL Server can help you find your way.

Let’s look at some variations of a comic book Top 10 list using ROW_NUMBER, RANK, and DENSERANK.

What is ROW_NUMBER?

The first way we can create a Top 10 list is with ROW_NUMBER. This option probably won’t be the best if you’re needing to deal with ties. If we want the Top 10 most recent comics in a collection based on year published, we can run:

SELECT Title, YearPublished, IssueNumber,
   ROW_NUMBER() OVER (ORDER BY YearPublished DESC) AS RN
FROM ComicBooks;
GO

Notice the first two comics are published in the same year. The same is true for rows 8 and 9. If you just need a general Top 10 and don’t care handling ties, this will work. Let’s look at some more options.

What is RANK?

Let’s use RANK instead of ROW_NUMBER() to create our Top 10:

SELECT Title, YearPublished, IssueNumber,
   RANK() OVER (ORDER BY YearPublished DESC) AS [Rank]
FROM ComicBooks;
GO

We have some differences. Our Rank column shows a 1 for the first two records since they have the same year. Our third record is still recorded as a rank of 3 rather than 2. Similarly, we have two records with a rank of 8 and the last record remains 10.

What is DENSERANK?

Our final option to review is DENSE_RANK:

SELECT Title, YearPublished, IssueNumber,
   DENSE_RANK() OVER (ORDER BY YearPublished DESC) AS DenseRank
FROM ComicBooks;
GO

Our first two records still have a value of 1 but look at the third record. Even though it’s the third record, it has a value of 2 due to the first two records coming in as a tie. All the way at the bottom, we see two records with a DenseRank of 7. This means the final record in our Top 10 ends up being 8.

Take Your Pick

Not all lists are the same. Consider the differences shown above when making your choice of ROW_NUMBER, RANK, and DENSE_RANK.

Thanks for reading!