SQL Server ROW_NUMBER SQL Function

In this guide, you’ll learn all about the ROW_NUMBER function in SQL Server.

What is the SQL ROW_NUMBER Function?

The ROW_NUMBER function in SQL Server is a function that adds a unique incrementing number to each row of your results. It essentially gives each of the rows a row number, starting from 1 and going up with each row.

If you want to order your results, you use the ORDER BY clause. However, this doesn’t show the number of each row. So, to see the row number in the results, you use the ROW_NUMBER function.

If you’re familiar with Oracle, it’s similar to the ROWNUM function in Oracle.

Now, let’s look at some examples.

 

Ordering Our Data

Let’s say we have a list of products in our database. We can select from the product table and order them from most expensive to least expensive.

SELECT
product_name,
price
FROM dbo.product
ORDER BY price DESC;

The results are:

product_name	price
Fridge	800
TV	600
Couch	450
Chair	80
Stool	80
Microwave	40
Kettle	25
Toaster	20

Now, what if we want to show the row numbers for each row? For example, show 1 for Fridge, 2 for TV, and so on?

Our IDE might show row numbers in the results, but this is only for display in the IDE and not a column in our results:

A Simple Example of ROW_NUMBER

Let’s see a simple example of the ROW_NUMBER SQL function.

We add the ROW_NUMBER function to our query.

SELECT
product_name,
price,
ROW_NUMBER()
FROM dbo.product
ORDER BY price DESC;

If we run this query, we’ll get an error:

[S0003][10753] The function 'ROW_NUMBER' must have an OVER clause.

The error message is pretty descriptive. The ROW_NUMBER function needs an OVER clause. This is used to define what set of data the row number is calculated.

So, we can add an OVER clause, and inside the brackets for the OVER clause we add an ORDER BY. This is how the row number is calculated. I’ve also added a column alias of rownum so it looks neater in the output.

SELECT
product_name,
price,
ROW_NUMBER() OVER (ORDER BY price DESC) AS rownum
FROM dbo.product
ORDER BY price DESC;

The results are:

product_name	price	rownum
Fridge	800	1
TV	600	2
Couch	450	3
Chair	80	4
Stool	80	5
Microwave	40	6
Kettle	25	7
Toaster	20	8

The ROW_NUMBER function has added the rownum column, and given each row a unique number starting with records with the highest price.

 

Example of ROW_NUMBER With Partition By

The ROW_NUMBER function is a type of window function, which means it can operate over a “window” or a range of data as well as the entire result set.

Window functions have an OVER clause with an ORDER BY inside it, as we’ve seen. They also have a PARTITION BY keyword.

The PARTITION BY keyword lets you define the “window” or the range of data to determine the row number of.

For example, let’s say we had the category of each product shown as well:

SELECT
product_name,
product_category_id,
price
FROM dbo.product
ORDER BY price DESC;

The results are:

product_name	product_category_id	price
Fridge	1	800
TV	2	600
Couch	2	450
Chair	2	80
Stool	2	80
Microwave	1	40
Kettle	1	25
Toaster	1	20

Now, let’s say we wanted to see the row number of each row within each product category. So, the highest priced product in category 1 would have a row number of 1, and the highest priced product in category 2 would also have a row number of 1.

The query would look like this. We use the PARTITION BY clause to specify the product_category_id should be used to partition the data for calculating the row number:

Our query looks like this:

SELECT
product_name,
product_category_id,
price,
ROW_NUMBER() OVER (
  PARTITION BY product_category_id
  ORDER BY price DESC
) AS rownum
FROM dbo.product
ORDER BY price DESC;

The results are:

product_name	product_category_id	price	rownum
Fridge	1	800	1
TV	2	600	1
Couch	2	450	2
Chair	2	80	3
Stool	2	80	4
Microwave	1	40	2
Kettle	1	25	3
Toaster	1	20	4

We can see that Fridge has a row number of 1 as it has the highest price in category 1. TV has a row number of 1 as well, because it’s in category 2 and is a separate partition or group for the ROW_NUMBER function. Couch has a row number of 2, because it’s in category 2 and comes after TV.

We can change the order that the data is displayed without impacting how the row number is calculated by updating the ORDER BY clause at the end of the query.

SELECT
product_name,
product_category_id,
price,
ROW_NUMBER() OVER (
  PARTITION BY product_category_id
  ORDER BY price DESC
) AS rownum
FROM dbo.product
ORDER BY product_category_id ASC, price DESC;

The results look like this:

product_name	product_category_id	price	rownum
Fridge	1	800	1
Microwave	1	40	2
Kettle	1	25	3
Toaster	1	20	4
TV	2	600	1
Couch	2	450	2
Chair	2	80	3
Stool	2	80	4

The same rows are shown, with the same row numbers, but they are just in a different order.

 

Using ROW_NUMBER for Pagination

We can also use the ROW_NUMBER function for pagination.

Pagination is the concept where you show a certain page of results. This could be search results (such as Google) or results of entries in the database like Stack Overflow questions.

The ROW_NUMBER function can be used to show page 1 of results (which may be rows 1 to 10), page 2 (rows 11 to 20) and so on.

Let’s see how to do that.

Let’s assume each page of products has 3 results. Yes, it’s a short page, but we have 8 products so we don’t have a lot to work with!

To show a page of results, we need to:

• Write a Select query that displays the row number
• Select from this select query where the row number is in a certain range

If we don’t do this in two steps, if we just select where the row number is in a range, it won’t show the right results.

So, our query to show page 1 of results (from rows 1 to 3) would look like this:

SELECT
product_name,
price,
rownum
FROM (
  SELECT product_name,
  price,
  ROW_NUMBER() OVER (ORDER BY price DESC) AS rownum
  FROM dbo.product
) sub
WHERE rownum >= 1
AND rownum <= 3
ORDER BY price DESC;

We have a subquery that finds the name, price, and calculates the row number. We then select from this subquery, and add a WHERE clause to show data where the row number is between 1 and 3.

The results are:

product_name	price	rownum
Fridge	800	1
TV	600	2
Couch	450	3

This is page 1 of the results. If we want to see page 2, we can change the WHERE clause to show different row numbers.

SELECT product_name,
price,
rownum
FROM (
  SELECT product_name,
  price,
  ROW_NUMBER() OVER (ORDER BY price DESC) AS rownum
  FROM dbo.product
) sub
WHERE rownum >= 4
AND rownum <= 6
ORDER BY price DESC;

The results are:

product_name	price	rownum
Chair	80	4
Stool	80	5
Microwave	40	6

This is page 2 of the results.

So, if you want to determine pagination in SQL Server, then use the ROW_NUMBER function in a subquery and filter it in the outer query with a WHERE clause.

 

ROW_NUMBER vs RANK vs DENSE_RANK

This ROW_NUMBER function sounds similar to the RANK and DENSE_RANK functions, doesn’t it?

They each work with data that has been ordered in the SELECT query, and each shows a number based on where the row is in the result.

However, the difference between these three functions occurs when there are duplicate values in the column being ordered.

• ROW_NUMBER will show unique numbers regardless of duplicates.
• RANK will show the same value then skip values if there are duplicates.
• DENSE_RANK will show the same value then not skip values if there are duplicates

Here’s an example:

SELECT
product_name,
price,
ROW_NUMBER() OVER (ORDER BY price DESC) AS row_num,
RANK() OVER (ORDER BY price DESC) AS rank_num,
DENSE_RANK() OVER (ORDER BY price DESC) AS drank_num
FROM dbo.product
ORDER BY price DESC;

This query uses ROW_NUMBER, RANK, and DENSE_RANK on the same set of data.

The results are:

product_name	price	row_num	rank_num	drank_num
Fridge	800	1	1	1
TV	600	2	2	2
Couch	450	3	3	3
Chair	80	4	4	4
Stool	80	5	4	4
Microwave	40	6	6	5
Kettle	25	7	7	6
Toaster	20	8	8	7

We can see the results of the three columns are mostly the same.

However, for the Stool product, which has the same price as Chair, ROW_NUMBER shows a number of 5 which is the next unique number. RANK and DENSE_RANk show it as 4 as it has the same price as Chair.

Then, the Microwave row has a ROW_NUMBER of 6 (the next unique number), RANK shows a value of 6 (because it skips values and resumes the ranking), and DENSE_RANK shows a value of 5 (because it does not skip values).

Hopefully, that explains the difference between these three functions.

 

Conclusion

The SQL Server ROW_NUMBER function is a window function that can be used to calculate a unique row number for each row in your results. It can also be used to calculate the row number within a group, and is slightly different to RANK and DENSE_RANK.