sql-servert-sqlpagination

How to select record from 5th row without using where query in SQL Server

I have a table and that has 10 records and fetch records from 5 to 10 without using where condition.

Solution

  • You can use OFSET-FETCH. It should be something like this:

    SELECT  *
FROM mytable
ORDER BY id
OFFSET 5 ROWS  
FETCH NEXT 5 ROWS ONLY;

    As the syntax above is supported from SQL Server 2012, for early version you can use ranking function:

    WITH DataSource AS
(
    SELECT *
          ,ROW_NUMBER() OVER(ORDER BY id) AS rn
    FROM mytable
)   
SELECT *
FROM DataSource
WHERE rn >= 5 AND rn <=10;