CODEWITHSUNDEEP
phpsqlsql-server
Klapsius
Klapsius

Reputation: 3359

PHP pagination using SQL SERVER 2008

I still trying to create pagination with PHP but SQL SERVER (2008) not support offset and limit. Below you can see SQL query but I don't know what is wrong:

declare @offset int 
declare @limit int 
set @offset = 2;
set @limit = 20;

SELECT *, ROW_NUMBER()  OVER (ORDER BY use_by_date desc ) AS RowNum FROM DB
WHERE RowNum >= @Offset
AND RowNum < @Offset + @Limit

Upvotes: 1

Views: 69

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

Just nest your query inside another one:

SELECT t.*
FROM (
   SELECT *, ROW_NUMBER()  OVER (ORDER BY use_by_date desc ) AS RowNum 
   FROM DB) t
WHERE t.RowNum >= @Offset AND t.RowNum < @Offset + @Limit

Windowed functions like ROW_NUMBER() can only appear in the SELECT or ORDER BY clauses.

Upvotes: 1

Related Questions