RowNumber in Select Query

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.

Abhishek Sur

Abhishek Sur is a Microsoft MVP since year 2011. He is an architect in the .NET platform. He has profound theoretical insight and years of hands on experience in different .NET products and languages. He leads the Microsoft User Group in Kolkata named KolkataGeeks, and regularly organizes events and seminars in various places for spreading .NET awareness. He is associated with the Microsoft Insider list on WPF and C#, and is in constant touch with product group teams. He blogs at http://www.abhisheksur.com His Book : Visual Studio 2012 and .NET 4.5 Expert Development Cookbook. Follow Abhishek at Twitter : @abhi2434