Row_Number SQL Server
SQL Server includes several Ranking Functions that can be called in T-SQL. One of these is the Row_Number() function. You can use this function to return a sequential number in your result set that begins at 1.
There are 2 arguments that can be passed into the function that determines how the data will be split. PARTITION BY divides the results set based on the from clause to determine how to apply the ROW_NUMBER function, this is an optional argument. ORDER BY determines how to sort the result set to apply the sequential numbers. For example if you want to get the row number of sales against all regions sorted in descending order. You would not need the PARTITION BY argument. However, if you wanted to see the row number within each region you would need to PARTITION BY the region to have the row numbers sequenced within each region.
Example 1 – Return the Row number for each row using order by. I want to assign each row a number based on the totaldue column in the Sales.SalesOrderHeader table sorted in descending order and display the associated territory ID column.
SELECT
ROW_NUMBER() over(order by totaldue desc) as row_no,
TerritoryID,
totaldue
FROM [AdventureWorks2014].[Sales].[SalesOrderHeader]
Results:
Example 2 – Return the Row number for each row using Partition by and order by. I want to assign each row a number based on the totaldue column in the Sales.SalesOrderHeader table for each territory id sorted in descending order. Using the partition by keyword in this example will start the row numbers for each territory id.
SELECT
ROW_NUMBER() over(partition by territoryid order by totaldue desc) as row_no,
TerritoryID,
totaldue
FROM [AdventureWorks2014].[Sales].[SalesOrderHeader]
Results:
As you can see there were 4594 rows for territoryid 1 and then terrritoryid 2 starts over with the number 1.
For full documentation see – http://msdn.microsoft.com/en-us/library/ms186734.aspx
