The SQL Server RANK function is a simple window function you should know to help gather meaningful information from your data.
Window functions are considered an advanced tool when querying data in SQL Server. If you’re trying to learn about window functions, congratulations, you’re moving up!
The RANK function is one of four ranking window functions we have available to us in Microsoft SQL Server. You should understand all the ranking window functions if you want to be a great database developer. Check out my full tutorial on ranking window functions to learn more:
SQL Server Ranking Window Functions: ROW_NUMBER, RANK, DENSE_RANK, NTILE
In this tutorial, we’re going to discuss what the RANK function is and how to use it.
Also, make sure you download your FREE Ebook:
FREE Ebook on SQL Server Window Functions!
This FREE EBook discusses absolutely everything you need to know about window functions in SQL Server, including everything discussed in this tutorial. This Ebook will definitely be a great resource for you to reference throughout your career as a data professional. Get it today!
We’ll discuss these topics:
- What is a window function?
- What is the RANK window function?
- Setting up some data
- Example of using RANK
- What’s the difference between RANK and DENSE_RANK?
- Tips, tricks, and links
Let’s get into it.
1. What is a window function?
Before we dive into the RANK function, we need to understand what a window function is in the first place.
A window function can be used to combine aggregate/ranking/offset information AND detail information in a single query.
Window functions are excellent for writing a single query that shows ranking information AND detail information in a single query.
To understand RANK, we need to understand terms like “window partition” and “window order clause“. If you don’t know what those are, you should definitely check out my complete introduction to window functions:
SQL Server Window Functions: An introduction for beginners
2. What is the RANK window function?
RANK will return an incrementing integer for every row in a window partition, starting with 1, according to an ordering you specify. It will produce the same rank value for rows with the same ordering value.
The RANK function does not take any arguments, and you do not specify a window frame.
One thing you need to make sure you do specify, however, is a window order clause. This makes perfect sense, because how are you supposed to rank things if they aren’t in some kind of order?
3. Setting up some data
To understand how the RANK function works, we need to set up some data. If you have been following along with any of the other window function tutorials, you will already have this data set up in your environment.
Run these SELECT statements to see if you already have the data:
SELECT * FROM Customers SELECT * FROM Products SELECT * FROM Orders
The data should look like this:
If you don’t have the data, let’s go ahead and add it. Here’s the Customers table:
--Create Customers table
CREATE TABLE Customers
(
CustID INT PRIMARY KEY IDENTITY(50,5),
FirstName VARCHAR(20),
LastName VARCHAR(20)
)
--Populate Customers it with data
INSERT INTO Customers(FirstName, LastName)
VALUES
('Joshua','Porter'),
('Andrew','Bluefield'),
('Jack','Donovan'),
('Cindy','Thatcher'),
('Gordon','Acres'),
('Gretchen','Hamilton')
The Products table:
--Create Products table
CREATE TABLE Products
(
ProductID INT PRIMARY KEY IDENTITY(20,2),
ProductName VARCHAR(20),
Price DECIMAL(5,2)
)
--Populate Products table with data
INSERT INTO Products (ProductName, Price)
VALUES
('Large Bench',198.00),
('Small Bench',169.40),
('Coffee Table',220.00),
('Side Tables',265.20),
('Coat Rack',45.00)
Finally, the Orders table:
--Create Orders table CREATE TABLE Orders ( OrderID INT PRIMARY KEY IDENTITY(100,10), CustID INT, ProdID INT, Qty TINYINT, Orderdate DATETIME ) --Populate Orders table with data INSERT INTO Orders(CustID, ProdID, Qty, Orderdate) VALUES (55, 22, 1, '6/1/2021'), (60, 28, 2, '6/6/2021'), (75, 26, 1, '6/13/2021'), (50, 20, 1, '7/1/2021'), (55, 28, 1, '7/6/2021'), (65, 24, 1, '7/14/2021'), (55, 26, 1, '7/18/2021'), (50, 26, 1, '7/24/2021'), (70, 24, 1, '8/6/2021'), (70, 26, 1, '8/6/2021'), (70, 22, 3, '9/1/2021')
Notice the Orders table contains unofficial foreign key links to both the Customers and Products tables. I say they are “unofficial” because I didn’t actually create a foreign key constraint between the tables. It’s not necessary for this tutorial.
Ok, now that we have some data set up, we can show you an example of using RANK.
4. Examples of using RANK
First, we’ll look at a very simple example of using the RANK function, then we’ll look at a more complex example using our data.
Simple example of using RANK
Let’s take a look at the Products table:
Maybe it would be nice to assign a rank to each of our Products according to how much each item costs. So for example, the most expensive item we sell would have a rank of 1. The next most expensive will have a rank of 2. So on and so forth, for all Products!
We can use the RANK function to easily see that information:
Let’s break down what we have here.
- Notice we call the RANK function in the SELECT list. You can only call the RANK function (or any other window function) in the SELECT list or the ORDER BY clause.
- The RANK() call itself does not have any arguments. It does not support using arguments.
- We use the OVER clause in all window functions. One could say it’s the heart of a window function.
- We specify our window order clause within the OVER clause. In our case, we’re ordering the data in our window by Price. In this case, we want to see the most expensive price on top, so we specify an order that is descending (DESC).
Since we chose not to outline a window partition clause within our OVER clause, the entire result set is considered one large partition, and therefore, the RANK function will operate on all rows in the result set.
Not sure what I’m talking about? It’s because you didn’t read the introduction to window functions tutorial!
Complex example of RANK
Let’s run a more complex SELECT statement with two JOINS to see some very useful information about each of our orders:
Here’s the query:
SELECT C.CustID, C.FirstName, C.LastName, P.ProductName, P.Price as 'Product Price', O.Qty, O.OrderDate FROM Orders as O INNER JOIN Customers AS C ON O.CustID = C.CustID INNER JOIN Products as P ON O.ProdID = P.ProductID
And the result set:
In this result set, we can see some really great details about each order. We can see the following details:
- Who made the order
- What they bought
- How much that product costs
- How many of that product they bought
- The date they made the order
So maybe we would like to rank each Order according to the sale price. That will be the cost of the Product multiplied by the quantity purchased.
Let’s first create a column that calculates sale price:
Cool, so now we know how much was spent for each Order. So again, we want to rank these rows according to the new Sale Price column. We want to give our highest sale a rank of 1, then our next highest a rank of 2, so on and so forth! Using the RANK function, we can do that easily:
Let’s talk about a couple of things:
- In our window order clause, we use the calculation of Price*Qty. We can’t use the alias ‘Sale Price‘ because SQL doesn’t let you reference a column alias in another column.
- Since we want the most expensive sale on top, we need to specify that we want the rows to be in descending order (DESC)
Understanding RANK
You probably noticed how several rows have the same rank. Several rows have a RANK value of 2, and there are two rows that have a RANK value of 6. You also might be wondering why there are gaps.
Let’s focus on the rows with a RANK value of 2:
We know the Sale Price of each of these rows is $265.20. These four rows are essentially tied in rank. It would not be appropriate for SQL Server to arbitrarily assign one of them a higher rank than the other, so SQL Server gives them the same rank.
Then we see there is a huge gap. The rank jumps from 2 to 6 for the next row, which is the Order for Cindy Thatcher.
When determining the rank to assign a row, SQL will look at the number of rows with a lower ordering value than the current row, then add 1 to it.
Remember, with window functions, rows are evaluated one at a time, from top to bottom. When SQL is processing the row for Cindy Thatcher’s Order, it’s finding how how many rows have a lower ordering value than this row. We can see it’s a count of 5 rows:
In other words, in our case, SQL is asking “How many rows have a higher Sale Price than Cindy Thatchers Order?”
The answer is 5.
By the way, please don’t get confused between “higher Sale Price“ and “lower ordering value“. In our case, the higher the Sale Price, the lower the ordering value 🙂
So when calculating the rank to assign to Cindy Thatcher’s Order, SQL will take the number of rows with a lower ordering value (5 in our case), then add 1 to it. Last time I checked, that adds up to 6, which is what we have as the RANK value!
And folks, the same thing is true of the other row with a RANK of 6. There are still 5 rows with a LOWER ordering value than that row.
Get it? Good.
5. What’s the difference between RANK and DENSE_RANK?
There is another ranking window function called DENSE_RANK. You might ask yourself, “What’s the difference”?
That’s a fine question, indeed. The difference is that DENSE_RANK will calculate the number of rows with a lower distinct ordering value than the current row, then add 1 to it.
Let’s add another column to calculate DENSE_RANK:
Ok, so now we need to think about distinctness when looking at the order for Cindy Thatcher.
How many distinct Sale Prices are higher than her Sale Price? Well, only 2.
The only 2 distinct Sale Prices that are higher than hers (and therefore have a lower ordering value) are $265.20 and $508.20.
So again, last time I checked, 2 + 1 is 3, which is what we see as her DENSE_RANK value!
This is exactly why it’s possible to have gaps with the RANK function, but not have gaps with the DENSE_RANK function. Use the one that suits your needs!
6. Tips, tricks, and links
Here is a list of some helpful tips and tricks you know when working with the RANK function:
Tip # 1: The RANK function supports a window partition clause
The examples we’ve looked at all did not have a window partition clause, meaning the entire result set is treated as one large partition.
You should know you can definitely specify a PARTITION clause when using RANK (or any other window function).
For example, what if we wanted to see the ranks of Sale Prices for each customer? In other words, for each customer, we want to see their Orders and how each Sale Price ranks.
It’s easy to add a PARTITION clause to see those details:
Now, we see a breakdown of ranks by customer. Nice!
Tip # 2: Remember, you can only use window functions in the SELECT list or ORDER BY clause.
All window functions can only be used in the SELECT list or the ORDER BY clause.
Tip # 3: Remember the rules about the RANK function.
The list of things to remember about the different window functions is long. Some require an argument, while some don’t. Some require a frame, while others don’t. For the RANK function, just remember these 3 rules:
- There are no arguments
- A window order clause is mandatory
- It does not support a window frame.
Links:
Everything discussed in this tutorial can be found in the following FREE Ebook:
Download your FREE SQL Server Window Functions EBook!
This FREE EBook discusses absolutely everything you need to know about window functions in SQL Server, including everything discussed in this tutorial. A proper understanding of window functions is essential for anyone looking to enter the field of data science. This eBook will definitely be a great resource for you to reference throughout your career. Get it today!
Next steps:
Leave a comment if you found this tutorial helpful!
Now that you understand RANK (and also DENSE_RANK) you should learn about the other ranking window functions available to us in Microsoft SQL Server.
You need to understand all the different ranking functions if you want to be a great database developer. Take a look at the full tutorial on all the ranking functions here:
SQL Server Ranking Window Functions: ROW_NUMBER, RANK, DENSE_RANK, NTILE
Also, if you missed the introduction to window functions, it discusses the different aggregate window functions you can use in SQL Server. That tutorial is a MUST-READ, because it discusses all the parts involved in a window function that you need to know in order to write a window function query correctly. Don’t skip that tutorial, you can find it here:
SQL Server Window Functions: An introduction for beginners
Thank you very much for reading!
Make sure you subscribe to my newsletter to receive special offers and notifications anytime a new tutorial is released!
If you have any questions, or if you are struggling with a different topic related to SQL Server, I’d be happy to discuss it. Leave a comment or visit my contact page and send me an email!
