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.

q1

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.

q2

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

q3

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

You may show it in this screen.
q4

Leave a Reply

Your email address will not be published.

Time limit is exhausted. Please reload CAPTCHA.