Click here to Skip to main content
Click here to Skip to main content

Tagged as

How To Select Record Based on Row Number

, 20 Jan 2010
Rate this:
Please Sign up or sign in to vote.
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 usingSELECT TOP 10 * FROM EMPBut 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...
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.
 
I think this would be helpful.
Cheers.
Rose | [Rose]

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Abhishek Sur
Architect
India India
Did you like his post?
 
Oh, lets go a bit further to know him better.
Visit his Website : www.abhisheksur.com to know more about Abhishek.
 
Abhishek also authored a book on .NET 4.5 Features and recommends you to read it, you will learn a lot from it.
http://bit.ly/EXPERTCookBook
 
Basically he is from India, who loves to explore the .NET world. He loves to code and in his leisure you always find him talking about technical stuffs.
 
Presently he is working in WPF, a new foundation to UI development, but mostly he likes to work on architecture and business classes. ASP.NET is one of his strength as well.
Have any problem? Write to him in his Forum.
 
You can also mail him directly to abhi2434@yahoo.com
 
Want a Coder like him for your project?
Drop him a mail to contact@abhisheksur.com
 
Visit His Blog

Dotnet Tricks and Tips



Dont forget to vote or share your comments about his Writing
Follow on   Twitter   Google+

Comments and Discussions

 
GeneralYes, I guess, you can easily select a portion of data for Pa... PinmemberAbhishek Sur17-Sep-10 9:01 
GeneralIts very usefull in case of webapplication...... PinmemberNabarun5828-Jul-10 2:48 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.140814.1 | Last Updated 20 Jan 2010
Article Copyright 2010 by Abhishek Sur
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid