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:
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.
Working as a VP product of
APPSeCONNECT, an integration platform of future, he does all sort of innovation around the product.
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