Select Records Based on Row Number in Sql Server

Posted By  abhi2434 On 02 Dec 2010 07:12:59
emailbookmarkadd commentsprint
No of Views:3533
Bookmarked:0 times
Votes:0 times

Introduction

 In this tips, i will explain how to select records based on Row number using ROW_NUMBER() function in sql server.It is a common issue of many of the guys on how we can fetch a record based on its record number. It is really easy to get first 10 records using

SELECT TOP 10 * FROM EMP;

But when we want to fetch records in such a way say I need to fetch record from 40 to 50. This comes very handy when you want your UI to display data in batch (say in multiple pages).

You can easily use the In built function ROW_NUMBER to handle this type of situation. First let me show the Record Number just associated with the Row :

SELECT ROW_NUMBER() OVER(ORDER BY EMPID DESC) AS 'ROWID', *
FROM [dbo].[EMP]

You can see that each record displays its Record Number in the column ROWID.

Always remember, you need to pass the Order By clause as it is mandatory. If you want to display record according to how the data is inserted in the table, add one column (Creation_DateTime) DateTime for which you put the current time and Order based on this Column.

Finally, to display record based on Row Number we join this table with the original.

SELECT A.* from [dbo].[EMP] A
INNER JOIN  
(SELECT ROW_NUMBER() OVER(ORDER BY EMPID DESC) AS 'RN', *
  FROM [dbo].[EMP]) B  ON A.EMPID= B.EMPID
AND B.RN between 20 and 30

You can see the records from 20 to 30.

References

I think this would be helpful.thank you for reading.

Sign Up to vote for this article
Other popular Tips/Tricks
    In this tips, I will explain how to ALTER or ADD two or more columns in to table by t-sql script.
    Published Date : 25/Mar/2011
    A very important feature of SQL Server 2008 is that we can enable CDC(Change Data capture) on database or table.We can track the database had CDC enabled by querying IS_CDC_ENABLED column
    Published Date : 17/Jan/2011
    A very important feature of SQL Server 2008 is that we can enable CDC(Change Data capture) on database or table.We can track the database had CDC enabled by querying IS_CDC_ENABLED column
    Published Date : 17/Jan/2011
    This is the third tips date related function in sql server. In this I going to show you how to use DATEDIFF() function in sql server. The DATEDIFF () is useful to get the specified date part between two dates
    Published Date : 16/Dec/2010
    The DATEADD function is a powerful built-in function sql server to add dates in different way and types. So I would like to explore in this tip how we can use the DATEADD function for different purpose.
    Published Date : 12/Dec/2010
Comments
There is no comments for this articles.
Leave a Reply
Title:
Display Name:
Email:
(not display in page for the security purphase)
Website:
Message:
Please refresh your screen using Ctrl+F5
If you can't read this number refresh your screen
Please input the anti-spam code that you can read in the image.
^ Scroll to Top
</