SQL Server 2008 pagination with total rows

Question

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'

Consider using OFFSET-FETCH if SQL version is 2012+

Lukasz Szozda
– Lukasz Szozda

2016-01-16 11:59:19 +00:00
Commented Jan 16, 2016 at 11:59 — Lukasz Szozda
– Lukasz Szozda, Commented Jan 16, 2016 at 11:59
Thanks, I should have mentioned it is 2008.

crankshaft
– crankshaft

2016-01-16 12:00:27 +00:00
Commented Jan 16, 2016 at 12:00 — crankshaft
– crankshaft, Commented Jan 16, 2016 at 12:00
Is SEQUENCE_NO unique?

Lukasz Szozda
– Lukasz Szozda

2016-01-16 12:02:05 +00:00
Commented Jan 16, 2016 at 12:02 — Lukasz Szozda
– Lukasz Szozda, Commented Jan 16, 2016 at 12:02

Pரதீப் · Accepted Answer · 2016-01-16 11:58:45Z

3

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

edited Jan 16, 2016 at 11:58

answered Jan 16, 2016 at 11:56

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

TheGameiswar · Accepted Answer · 2023-05-19 17:32:02Z

0

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;

edited May 19, 2023 at 17:32

answered Jan 16, 2016 at 13:59

TheGameiswar

29k9 gold badges67 silver badges106 bronze badges

Collectives™ on Stack Overflow

SQL Server 2008 pagination with total rows

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related