1

To paginate a select query normally I would do this:

SELECT * FROM items WHERE condition = 1 LIMIT $start, $items

However, in MSSQL there is no LIMIT condition, so I tried with:

SELECT * FROM items WHERE condition = 1 OFFSET $start ROWS FETCH NEXT $items ROWS ONLY

And I get the error:

42000 - [SQL Server]Invalid usage of the option NEXT in the FETCH statement.

So what exactly should I do to paginate results?

Net Jacker
  • 41
  • 5
  • Possible duplicate of [How to implement LIMIT with Microsoft SQL Server?](https://stackoverflow.com/questions/603724/how-to-implement-limit-with-microsoft-sql-server) – HPierce Jan 11 '18 at 02:15

2 Answers2

4

This may help you. The OFFSET-FETCH clause provides you with an option to fetch only a window or page of results from the result set. OFFSET-FETCH can be used only with the ORDER BY clause.

SELECT P_Name FROM ITEMS ORDER BY  P_Name OFFSET 10 ROWS;

SRC: https://technet.microsoft.com/en-us/library/gg699618.aspx

mrtig
  • 2,217
  • 16
  • 26
Zayn Korai
  • 493
  • 1
  • 6
  • 24
  • Nevermind, it executes the query now, but the pagination does not work. It selects always the same fields, even if $start is different... – Net Jacker Jan 11 '18 at 05:16
0

The MS SQL equivalent to MySQL's LIMIT clause is TOP. 

SELECT TOP 10 * FROM items WHERE condition = 1;

Will return the top ten rows, the same way it is done with LIMIT here:

SELECT * FROM items WHERE condition = 1 stuff LIMIT 10;

For more details (since I don't know all the context of your code) you can take a look here.

Geshode
  • 3,600
  • 6
  • 18
  • 32