What Is Rank?

Introduced to SQL in 2005, the Rank function has become a hugely beneficial database tool, especially for composing reports based on database information.

However, despite existing for nearly two decades, there is still a lot of confusion surrounding Rank and neighbor tool, Dense_Rank.

So, to ensure you don’t miss out on these awesome database management utensils, let’s discuss what Rank is exactly, and, of course, how you can work it into your SQL statements and utilize it yourself. Sound good? Good. Let’s get to it!

The Rank Function At A Glance

Ranking in the context of databases is precisely what it sounds like. It’s a tool in SQL that we can use to retrieve information and have it presented to us in a ranked order. 

We decide which data type or field is used to order the ranking when building our SQL query.

Rank vs Dense_Rank: What’s The Difference?

The fundamental difference between Rank and Dense_Rank is whether ties in the data used to rank a list are skipped in the ranking, which sounds far more confusing than it is. Here, I’ll show you…

Let’s say we’re ranking five rows of data. For the sake of the example, we’ll say that we’re ranking the highest paid employees in a workplace, and we only need to know employee name and salary. Our results should come out looking like this…

Pretty straightforward, but what happens if two of our 5 top-paid employees are on the same wage? Well, if we used the Rank keyword in our SQL query, it would look a little something like this…

This is what I mean by skipping rank. As the second employee is on the same wage as the first, they’re both ranked as number 1, which causes the results to skip number 2 and move straight on to number 3.

The Dense_Rank keyword, on the other hand, does not skip rank in the face of ties, meaning it would look like this…

You see? Not so complicated after all. It’s just easier to be shown than told.

One last thing before we move on; as long as you get the syntax right, you can absolutely use both Rank and Dense_Rank in the same query, saving you from making two separate data requests. 

Now let’s take a look at how these keywords are implemented in SQL syntax.

SQL Rank Syntax

As we’re posing a query here, we’ll have to begin our data request the same way we do with all queries, and that’s by telling our SQL server what information (or fields) we want it to pull up for us, and from which table.

This is done using the keyword, “From”, followed by a table name (we’ll call ours tblEmployees) then “Select”, followed by the desired fields.

So, using our employee data example from earlier, let’s say that, this time, we want all information fields to come up when we execute our SQL query, including Employee Name, Employee Gender, Employee Tenure, and Employee Salary.

The first two lines of our query would look like this…

From tblEmployees

SELECT *,

The asterisk is just a quick way of asking for all the fields to be included in a query, saving us from typing them out individually. 

Next up, we have to introduce the Rank or Dense Rank keyword, followed by a pair of brackets, and then the “Over” keyword, leaving our query looking like this…

From tblEmployees

SELECT *,

RANK () OVER

In this example, I have chosen Rank over Dense Rank.

The “Over” keyword is like a bridge connecting our demand that information be ranked, to the data it should be ranked by. So, now we have to establish what data that is. 

In this scenario, we want our employees ranked by salary, and we do that by opening some brackets, using the keyword “Order By”, typing the name of the field, and following it up with the keyword “Desc”, as we want the info in descending order.

Our query should now look like this…

From tblEmployees

SELECT *,

RANK () OVER (ORDER BY Employee Salary Desc)

But we’re not quite done yet. Our Rank column needs a name, so let’s add one after the brackets by typing “AS [Rank]”.

Our column will now be labeled “Rank”, and the query will look like this…

From tblEmployees

SELECT *,

RANK () OVER (ORDER BY Employee Salary Desc) AS [Rank]

If you wanted to use Dense_Rank instead of Rank, you’d follow the exact same principles.

Now let’s take a look at the results table our query would produce:

Rank Partitions

Another neat thing we can do with the Rank and Dense_Rank keywords is establish a partition in the ranking, which is achieved by placing the keyword “Partition By”, followed by the field we want the data to be separated by, before the “Order By” keyword.

Let’s say, for example, that we wanted to split our salary rankings by employee gender; our query would look a little something like this…

From tblEmployees

SELECT *,

RANK () OVER (PARTITION BY Employee Gender ORDER BY Employee Salary Desc) AS [Rank]

This query would then pull up the following table of ranked results:

Final Thoughts

And that’s basically all there is to it — the Rank function of SQL ranks query results using a field of your choice as a reference for the ranking… simple! Now you know what it is and how it’s done, you can give it a go yourself!

• Author
• Recent Posts

Albert Niall

Hi, I’m Albert Niall, and I want people to make the most out of their online presence and use it to their advantage.

Here at Open Query I want to make it as easy as possible for you to learn about topics that might not be easily accessible elsewhere online. I don’t know about you, but I haven’t seen many other websites talking about the MariaDB Foundation and its updates.

That’s what makes Open Query truly unique. While others might assume everyone knows this information already automatically, I am filling my online resource to help.

Latest posts by Albert Niall (see all)

• What Is Percentile Rank? - February 15, 2022
• How To Use Distinct In SQL - February 14, 2022
• How To Drop A Column In SQL - February 14, 2022