SQL Server : Displaying records with Sequential Numbering / Group wise Sequential Number / specific range of records in SQL Server using ROW_NUMBER() function.

One of my SQL buddy called yesterday and asked me, how to display the records with Serial Numbers in SQL Server.  I gave the solution over phone which I want to share with you guys too. :)

Assume that we have a table with the following records.

 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 




How to display the records with record number :

Now, let me show you how to display the same records with Serial Number (Record Number) using ROW_NUMBER() function.

SELECT
ROW_NUMBER() OVER (ORDER BY [CityName] ASC ) AS 'Record Number',
[CityName],[StateName]
FROM
[DBO].[City] WITH (NOLOCK)
GO

You can see the records with serial number as below.

How to display group wise record number:

In a similar way, we can reset the record numbers based on group. Here, I am going to display the record numbers based on StateName wise.


SELECT
ROW_NUMBER() OVER ( PARTITION BY [StateName] ORDER BY [CityName]) AS 'Record Number', [CityName],[StateName]
FROM
[DBO].[City] WITH (NOLOCK)
ORDER BY
[StateName],[CityName]


Now,  see the group wise record number result below.

How to display specific range of records from the result set:

Sometimes, It may require to display the records for some specific range. Let us say, I want to display the records range between 5 to 10.


WITH CTECity (RecordNumber,CityName, StateName)
AS
(SELECT
ROW_NUMBER() OVER (ORDER BY [CityName] ASC ) AS RecordNumber,
[CityName],[StateName]
FROM
[DBO].[City] WITH (NOLOCK)
)
SELECT
[CityName],[StateName]
FROM
CTECity
WHERE
[RecordNumber] BETWEEN 5 AND 10
GO

Now, See the result below and you can compare the result with my first example.

 
 
 
 
 
 
 
Note :

(a). This example will work only in SQL Server 2005 and above versions.
(b). ROW_NUMBER() function returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.
(c). For my last example, I used Common Table Expression (CTE) which was introduced in SQL Server 2005 and it’s a "temporary result set" that exists only within the scope of a single SQL statement.