0
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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Iwnow
  • 143
  • 2
  • 5
  • 12
  • 1
    If you are on 2012 or later you might consider a different approach entirely. https://www.mssqltips.com/sqlservertip/2362/overview-of-offset-and-fetch-feature-of-sql-server-2012/ – Sean Lange Jul 06 '16 at 20:15
  • Possible duplicate of [SQL Server query for paging](http://stackoverflow.com/questions/38231394/sql-server-query-for-paging) – Alex Kudryashev Jul 06 '16 at 20:25
  • This is duplicate of http://stackoverflow.com/questions/38231394/sql-server-query-for-paging asked 1 hour ago. – Alex Kudryashev Jul 06 '16 at 20:26
  • See answer http://stackoverflow.com/questions/38231394/sql-server-query-for-paging/38231787#38231787 – Alex Kudryashev Jul 06 '16 at 20:27

2 Answers2

0

you need to change the order of the inner and outer query

select 10-20

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

While I used this method in 2002 it is now silly with the availability of windowing functions. I think that is a better way.

Hogan
  • 69,564
  • 10
  • 76
  • 117
  • To retrieve records 10-20 you actually don't need to get 20 records but 10. – derloopkat Jul 06 '16 at 20:33
  • @derloopkat - you are wrong, this query gets the bottom 10 of the top 20 This is how it goes from 10-20. This is exactly what the original question was trying to understand. Please feel free to post some code that does so without a "get 20 records". Of course (as I said this was from 2002) now you can use windowing functions (Kostya's answer) or the fetch keyword so you can't get around it those ways as you explain to me how to do this without needing to fetch 20 records. – Hogan Jul 07 '16 at 03:45
  • select top 10 * from dborders where id>@previousPageLastID order by id – derloopkat Jul 07 '16 at 10:19
  • @derloopkat - cute, but clearly not within the scope of the question. – Hogan Jul 07 '16 at 20:42
0

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]
Kostya
  • 1,567
  • 1
  • 9
  • 15