CODEWITHSUNDEEP
sqlsql-serversql-server-2008
crankshaft
crankshaft

Reputation: 2677

SQL Server 2008 pagination with total rows

I see there are some similar questions, but I was unable to find an answer that I understood as I am not a SQL query expert.

This currently works to get the page of records:

WITH PAGE AS 
(
   SELECT 
       ROW_NUMBER() OVER (ORDER BY SEQUENCE_NO ASC) AS _ROW_, * 
   FROM mytable
) 
SELECT * 
FROM PAGE 
WHERE _ROW_ BETWEEN 0 AND 25

But how can I modify this so that it also returns the total number of records that matched the first query ?

WITH PAGE AS 
(
   SELECT 
       ROW_NUMBER(), COUNT(*) OVER (ORDER BY SEQUENCE_NO ASC) AS _ROW_, _TOTAL_, * 
   FROM mytable
) 
SELECT * 
FROM PAGE 
WHERE _ROW_ BETWEEN 0 AND 25

I get the following error:

Incorrect syntax near 'ROW_NUMBER', expected 'OVER'

Upvotes: 1

Views: 309

Answers (2)

TheGameiswar
TheGameiswar

Reputation: 28900

version using offset and Fetch

select 
 * from 
test1
order by id 
offset 0 rows fetch first 10 rows only;

Above query orders by id column and doesn't skip(offset) any rows and gives me first 10 rows.

select 
 * from 
test1
order by id 
offset 10  rows fetch next 10 rows only;

Above query skips first 10 rows and gives me next 10 rows based on id column order

we also can use variables

declare @offset int
declare @fetch int

Set @offset=10
set @fetch =10

    select 
     * from 
    test1
    order by id 
offset (@offset)  rows fetch first (@fetch) rows only;

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93724

That is not the right syntax, You need to use another Over clause for count

Try this

;WITH PAGE AS 
(
SELECT ROW_NUMBER() OVER(ORDER BY SEQUENCE_NO ASC) AS _ROW_,
       COUNT(*) OVER() AS _TOTAL_, * 
FROM mytable
) 
SELECT * FROM PAGE WHERE _ROW_ BETWEEN 0 AND 25

Upvotes: 3

Related Questions