Using the Row_Number Function in SQL Server
Transact-SQL has several ranking functions that allow you to have a ranking value for each row returned by a query. One of these that I get a lot of use with is the Row_Number function. This article will show you the Row_Number function and how you might find it useful.
Have you ever been called on to create a list of items from a database with a sequential number for each of the items? This is often used for certain types of printed reports. The Row_Number function gives you this sequential number.
The formal syntax is this:
ROW_NUMBER ( ) OVER ( [ PARTITION BY value_expression , ... [ n ] ] order_by_clause )
When you apply a sequential row number to a result set, the ordering of the numbers that are output is determined by the ORDER BY clause in parentheses, which is a required part of the syntax. What this means is that you can specify the order of the numbering (i.e. which record gets the number 1, which gets the number 2 etc.) separately from the ordering of the query itself.
Here is a basic example, using the AdventureWorks database. This query returns mountain bike related products. The Row_Number function outputs a sequential number for each record, based on ordering by the product name. Note that this is the same regardless of any ORDER BY clause at the end of the query.
SELECT [ProductID]
,[Name]
,[ProductNumber]
,ROW_NUMBER() OVER (ORDER BY [Name]) AS Name_Seq_Number
FROM [AdventureWorks2012].[Production].[Product]
WHERE ProductLine = 'M'
The results of the query looks like this:
ProductID Name ProductNumber Name_Seq_Number 879 All-Purpose Bike Stand ST-1401 1 878 Fender Set - Mountain FE-6654 2 863 Full-Finger Gloves, L GL-F110-L 3 862 Full-Finger Gloves, M GL-F110-M 4 861 Full-Finger Gloves, S GL-F110-S 5 747 HL Mountain Frame - Black, 38 FR-M94B-38 6 743 HL Mountain Frame - Black, 42 FR-M94B-42 7 744 HL Mountain Frame - Black, 44 FR-M94B-44 8 746 HL Mountain Frame - Black, 46 FR-M94B-46 9 745 HL Mountain Frame - Black, 48 FR-M94B-48 10 ..........
Notice that since there is no ORDER BY clause for the query, the results are output in sequential number order. And since the sequential numbers were determined by ordering the Name column, the results are sorted by the Name column.
Here is the same query, but with a second Row_Number function added, this time ordering by the Product Number.
SELECT [ProductID]
,[Name]
,[ProductNumber]
,ROW_NUMBER() OVER (ORDER BY [Name]) AS Name_Seq_Number
,ROW_NUMBER() OVER (ORDER BY [ProductNumber]) AS ProductNumber_Seq_Number
FROM [AdventureWorks2012].[Production].[Product]
WHERE ProductLine = 'M'
And in the output, you can see that the sequential numberings are correct for their ordering, even though the final query output is not in the same order.
ProductID Name ProductNumber Name_Seq_Number ProductNumber_Seq_Number 871 Mountain Bottle Cage BC-M005 54 1 989 Mountain-500 Black, 40 BK-M18B-40 79 2 990 Mountain-500 Black, 42 BK-M18B-42 80 3 991 Mountain-500 Black, 44 BK-M18B-44 81 4 992 Mountain-500 Black, 48 BK-M18B-48 82 5 993 Mountain-500 Black, 52 BK-M18B-52 83 6 984 Mountain-500 Silver, 40 BK-M18S-40 84 7 985 Mountain-500 Silver, 42 BK-M18S-42 85 8 986 Mountain-500 Silver, 44 BK-M18S-44 86 9 987 Mountain-500 Silver, 48 BK-M18S-48 87 10 .............
One important thing to note is that you want to use the Row_Number function with columns that contain unique values (at least within the parameters of the query). If there were two or more records with the same value (e.g. two records with a Product Number of BC-M005) then there is no guarantee of which record will get which number. And it may be different for each run of the query.
While this function is useful as shown so far, there may be times where you want a sequential numbering, but you want the number to restart for groups of records within the result set. This is where the PARTITION BY clause comes in to play.
Suppose you need to create a query to show sales order details for mountain bike related products, and you want to have a sequential number for the products, FOR EACH SALES ORDER. To do this, you use the PARTION BY clause to group, or partition, the records by sales order, using the SalesOrderID column. Then within each grouping, you order the line items by the product Name column.
SELECT SD.[SalesOrderID]
,SH.OrderDate
,P.[Name]
,[OrderQty]
,ROW_NUMBER() OVER (PARTITION BY SD.SalesOrderID ORDER BY P.[Name]) AS Item_Number
FROM [Sales].[SalesOrderDetail] SD
INNER JOIN [Sales].[SalesOrderHeader] SH ON SD.SalesOrderID = SH.SalesOrderID
INNER JOIN [Production].[Product] P ON SD.ProductID = P.ProductID
WHERE ProductLine = 'M'
ORDER BY SH.OrderDate
SalesOrderID OrderDate Name OrderQty Item_Number 43665 2005-07-01 00:00:00.000 Mountain Bike Socks, M 6 1 43665 2005-07-01 00:00:00.000 Mountain-100 Black, 38 2 2 43665 2005-07-01 00:00:00.000 Mountain-100 Black, 42 1 3 43665 2005-07-01 00:00:00.000 Mountain-100 Black, 44 2 4 43665 2005-07-01 00:00:00.000 Mountain-100 Black, 48 1 5 43665 2005-07-01 00:00:00.000 Mountain-100 Silver, 44 1 6 43670 2005-07-01 00:00:00.000 Mountain Bike Socks, L 1 1 43670 2005-07-01 00:00:00.000 Mountain Bike Socks, M 2 2 43670 2005-07-01 00:00:00.000 Mountain-100 Black, 42 1 3 43670 2005-07-01 00:00:00.000 Mountain-100 Silver, 44 2 4 43672 2005-07-01 00:00:00.000 Mountain Bike Socks, M 6 1 43672 2005-07-01 00:00:00.000 Mountain-100 Black, 42 2 2 43672 2005-07-01 00:00:00.000 Mountain-100 Silver, 48 1 3 43695 2005-07-01 00:00:00.000 HL Mountain Frame - Black, 38 1 1 43695 2005-07-01 00:00:00.000 HL Mountain Frame - Silver, 38 2 2 43695 2005-07-01 00:00:00.000 HL Mountain Frame - Silver, 46 1 3 43695 2005-07-01 00:00:00.000 Mountain Bike Socks, M 2 4 43695 2005-07-01 00:00:00.000 Mountain-100 Black, 38 5 5 43695 2005-07-01 00:00:00.000 Mountain-100 Black, 44 5 6 43695 2005-07-01 00:00:00.000 Mountain-100 Black, 48 6 7 43695 2005-07-01 00:00:00.000 Mountain-100 Silver, 48 2 8 ................................................
In the results, you can see that the numbering is restarted for each sales order.
Row_Number is one of the SQL Server ranking functions. You may also want to check out RANK, DENSE_RANK, and NTILE functions. The OVER clause is also used in aggregate and analytic functions and is a very powerful tool if you understand how to use it. Look for more articles here on the OVER clause.