0

i am developing an application and in my scenario i have a table BookTable which does not have a primary key (atleast not till now) and i want to be able to retrieve records like first the top 10, then from 11-20 and then 21-30, i hope i am making my point clear here. i have looked over google and so far have been unsuccessful in finding any solution. hope i will get help here. Thanks. P.S i am working with MS SQL Server 2012

Necreaux
  • 9,451
  • 7
  • 26
  • 43
Muhammad Salman
  • 39
  • 6
  • This is RDBMS-specific. There's many questions on this site that explain how to skip rows and how to keep a fixed number of rows. – Sergey Kalinichenko Apr 15 '15 at 15:35
  • edited the question and added that i am working with MS SQL Server 2012 – Muhammad Salman Apr 15 '15 at 15:45
  • I can only caution you of using OFFSET pagination. It is very bad way in general case, good pagination can be achived only through index range scans, see Markus Winand bllog for this http://use-the-index-luke.com/no-offset – Ilia Maskov Apr 15 '15 at 15:46
  • possible duplicate of [What is the best way to paginate results in SQL Server](http://stackoverflow.com/questions/109232/what-is-the-best-way-to-paginate-results-in-sql-server) – Tab Alleman Apr 15 '15 at 20:25

2 Answers2

1

A bit of googling leads me to believe that this will work in MSSQL 2012.

SELECT a, b FROM Table 
ORDER BY a
OFFSET 50 ROWS FETCH NEXT 25 ROWS ONLY

The documentation says that it can only be used if you have an ORDER BY clause.

Dai
  • 1,510
  • 1
  • 11
  • 12
  • i dont have a primary key and for some reason i dont want to change the order the rows were added in. but now your post has led me to think i can add an empty column and order on basis of that and see what happens ;) – Muhammad Salman Apr 15 '15 at 15:46
1
Select Top(@numRegPerPage) ROW_NUMBER() OVER(ORDER BY myOrderField ASC) as NUM_REG
Where NUM_REG > @lastNumReg
mggSoft
  • 992
  • 2
  • 20
  • 35
  • This has already been accepted, but can you reference the results of a row_number function in the where clause directly? Don't you need to wrap in a CTE? – Kevin Suchlicki Apr 15 '15 at 17:47
  • Short answer, NO. You can only use ROW_NUMBER() and other windows functions in the SELECT clause and ORDER BY clause. Instead of a CTE you could use a subquery, but that's like the same thing, referencing it indirectly. – Stephan Apr 15 '15 at 18:01
  • It'is perfomance killer solution in case of big table, no matter what page you select, all data before this page will be readed from storage. NO OFFSET PAGINATION, for more info http://use-the-index-luke.com/no-offset – Ilia Maskov Apr 16 '15 at 09:23