The SQL Server ROW_NUMBER function is a simple window function you should know to help you 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 ROW_NUMBER 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 very brief tutorial, we’ll discuss what the ROW_NUMBER 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 ROW_NUMBER window function?
- Setting up some data
- Example of using ROW_NUMBER
- Tips, tricks, and links
Let’s get into it.
1. What is a window function?
Before we dive into the ROW_NUMBER 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 ROW_NUMBER, 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 ROW_NUMBER window function?
The ROW_NUMBER function simply gives you a unique incrementing integer for every row in a window partition.
The ROW_NUMBER function does not take any arguments, and you don’t 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? Gnomesayin?
3. Setting up some data
To understand how the ROW_NUMBER 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 ROW_NUMBER.
4. Examples of using ROW_NUMBER
First, we’ll look at a very simple example of using the ROW_NUMBER function, then we’ll look at a more complex example using our data.
Simple example of using ROW_NUMBER
Let’s take a look at the Customers table:
What if we wanted to give each row a row number, starting from the smallest CustID and ending with the largest? We would ideally want to assign the first row in this result set a row number of 1, and have each row after incremented by 1.
Using the ROW_NUMBER function, we can do that very easily:
Let’s break down what we have here.
- Notice we call the ROW_NUMBER function in the SELECT list. You can only call the ROW_NUMBER function (or any other window function) in the SELECT list or the ORDER BY clause.
- The ROW_NUMBER() 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 CustID.
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 ROW_NUMBER 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!
Don’t rely on the automatic row numbers SQL gives you
Now, maybe you’re looking at the result set any saying, “Bro, those numbers in your stupid ‘Row Number’ column are the same numbers seen all the way to the left”
These numbers:
So you might be saying, “Why are you wasting time gathering numbers that SQL gives you already?”
Bro, it’s because those numbers aren’t part of the result set. If I wanted to export this data to an excel spreadsheet, those numbers on the left would not come over. But the numbers in our ‘Row Number‘ column will.
Here is a screenshot of what’s seen in an Open Office spreadsheet when I copy the data over:
Get it? Good.
Complex example of ROW_NUMBER
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 see a row number assigned to each Order according to the date it was placed (in order by OrderDate).
We can use the ROW_NUMBER function again to give us that information:
Great, now each row has a unique row number in line with the OrderDate value.
The great thing about window functions is that we can introduce a window partition clause. What if we wanted to see row numbers for each customer? In other words, for each customer, we want to see row numbers that align with when they placed each order.
If we put a partition clause by CustID, we’ll see a breakdown for each customer:
Now, we see a breakdown for each individual customer. We assign a unique row number to each row for each customer, which might be useful to see. For example, if we want to know the very first product all our customers bought from us, we can simply locate the row for each customer where the RowNumber value is 1.
But truly, the FIRST_VALUE function would be better to use if that’s what you want to see 🙂
Superb!
5. Tips, tricks, and links
Here is a list of some helpful tips and tricks you know when working with the ROW_NUMBER function:
Tip # 1: 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 # 2: Remember the rules about the ROW_NUMBER 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 ROW_NUMBER 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!
The ROW_NUMBER function is one of four ranking window functions available to us in Microsoft SQL Server. You need to understand all the different ranking functions if you want to be the world’s okay-est 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!
