Query similar to TOP 10 to select inbetween records?

Question

I have 100 records in which i can select top 10 using the "TOP 10" in the query. Similarly is there anything to get the 20th to 30th record?

Answer 1

Since you are using SQL Server 2005 you can use the "new" ROW_NUMBER() function. This will give you row 11 to 20:

SELECT  Description, Date
FROM     (SELECT  ROW_NUMBER() OVER (ORDER BY Date DESC)
             AS Row, Description, Date FROM LOG)
            AS LogWithRowNumbers
WHERE  Row >= 11 AND Row <= 20

If you have a lot of records, using TOP X in the inner SELECT clause may speed up things a bit as there is no use returning 1000 records if you are only going to grab records 11 through 20:

SELECT  Description, Date
FROM     (SELECT TOP 20 ROW_NUMBER() OVER (ORDER BY Date DESC)
             AS Row, Description, Date FROM LOG)
            AS LogWithRowNumbers
WHERE  Row >= 11 AND Row <= 20

Source

Answer 2

You should use ROWNUMBER() for get record number of resultset and filter it in where cause.

SELECT * FROM   (SELECT ROW_NUMBER() OVER(ORDER BY name) AS
rownum, name , age FROM Table) AS Table1
WHERE rownum >= 2 AND rownum <= 5

----------------------------
rownum     | name |   age   |
----------------------------
   2     |John    |   25    | 
   3     |Adam    |   23    | 
   4     |Tom     |   19    |
   5     |Jame    |   22    |
----------------------------