Using ROW NUMBER() function of Transact-SQL
ROW_NUMBER() in Transact-SQL returns sequential number of row in set of data from database, it returns bigint
datatype as number.
The first number is 1. After ROW_NUMBER() using OVER clause with ORDER BY clause is required.
Values of columns in the ORDER BY
must be unique.
This simple version of ROW_NUMBER I used in query get data from AdventureWork2012
database.
The first example get data about people by joining 3 tables.
SELECT p.LastName, p.FirstName, p.Title, a.City FROM [AdventureWorks2012].[Person].[BusinessEntityAddress] bea JOIN [AdventureWorks2012].[Person].[Person] p ON bea.BusinessEntityID = p.BusinessEntityID JOIN [AdventureWorks2012].[Person].[Address] a ON bea.AddressID = a.AddressID ORDER BY a.City,p.LastName, p.FirstName
The data are sort by city,last and first name people.
The second example numbers rows ordered them by city
column.
SELECT row_number() over( order by a.City ) as row, p.LastName, p.FirstName, p.Title, a.City FROM [AdventureWorks2012].[Person].[BusinessEntityAddress] bea JOIN [AdventureWorks2012].[Person].[Person] p ON bea.BusinessEntityID = p.BusinessEntityID JOIN [AdventureWorks2012].[Person].[Address] a ON bea.AddressID = a.AddressID
In this result set a one row has unique number. You may see that names are not sorted in alphabetic order.
Next example sorts these data by city
and names columns.
SELECT row_number() over( order by a.City, p.LastName, p.FirstName ) as row, p.LastName, p.FirstName, p.Title, a.City FROM [AdventureWorks2012].[Person].[BusinessEntityAddress] bea JOIN [AdventureWorks2012].[Person].[Person] p ON bea.BusinessEntityID = p.BusinessEntityID JOIN [AdventureWorks2012].[Person].[Address] a ON bea.AddressID = a.AddressID
For ROW_NUMBER it is possible to number rows in the subset of data separately. In this case You must use PARITION BY
clause in OVER
clause. PARTITION BY clause is not required and devided data in subset of data according to given column.
In example below the data are number apart for each city.
SELECT row_number() over( PARTITION BY a.City ORDER BY p.LastName, p.FirstName ) as row, p.LastName, p.FirstName, p.Title, a.City FROM [AdventureWorks2012].[Person].[BusinessEntityAddress] bea JOIN [AdventureWorks2012].[Person].[Person] p ON bea.BusinessEntityID = p.BusinessEntityID JOIN [AdventureWorks2012].[Person].[Address] a ON bea.AddressID = a.AddressID