Iwnow Iwnow - 5 months ago 8
SQL Question

MS SQL, pagination mechanism

SELECT TOP 10 * FROM (
SELECT TOP 100 [ID] ,[SUMMARY] ,[NAME]
FROM [db_test].[dbschema].[dborders]
ORDER BY [ID]
)AS X ORDER BY [ID]


I got this query for pagination mechanism, how to query result from 10 to 20 record?
Because when I change first number from 10to 20 I get first 20 records, not records between 10 and 20.
Please write example query for me.

edit: it's not SQL Server 2012

Answer

you could assign a page using row_number like

SELECT * FROM (
    SELECT TOP 100 [ID] ,[SUMMARY] ,[NAME]   ,row_number () over (order by [ID])/10 + 1 as [Page]
FROM [db_test].[dbschema].[dborders])AS X 
where [Page] = 2
ORDER BY [ID]