Wednesday, April 28, 2010

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.

8 comments:

  1. Nice article.Please post some more articles on sql server 2008.

    ReplyDelete
  2. Thanks for the article. It is very helpful!

    ReplyDelete
  3. Elegant solution and just what I needed. I couldn't find the solution anywhere else. Thanks!

    ReplyDelete
  4. Thank you so much :). It helped me lot...

    ReplyDelete
  5. hi,

    is this possible in order like:
    1 AP (Andhra Pradesh)
    1 AP
    1 AP
    2 TN
    2 TN
    3 WS
    3 WS

    Please suggest:

    Regards:
    Subhash

    ReplyDelete
  6. Hi Subhash,

    Yes, Its possible. Try like this,

    SELECT
    DENSE_RANK() OVER (ORDER BY [STATENAME]) AS RecordNumber, [STATENAME]
    FROM
    [DBO].[STATE]

    For more info, please go through
    http://sivasqlbi.blogspot.com/2012/05/sql-server-find-out-nth-highest-salary.html

    ReplyDelete
  7. thanks a lot... SV...

    ReplyDelete
  8. Thankfulness to my father who shared with me regarding this weblog, this web site is
    truly amazing.

    Here is my site: open minecraft servers

    ReplyDelete