Paging with ROW_NUMBER on filtered records (SQL SERVER 2005)

7 Sep 2006 4:05 PM

bert.vanderauwera

Hi,

I've got the following question. I always used this technique with
temporary tables to cope with paging in sql server 2000 but now that we
got sql 2005 running in our company i've got to rewrite the paging
stored procedures to a version that uses ROW_NUMBER.

Basically, this is the sql statement that i use now to get 10 records
back:

WITH Klanten AS (
SELECT ROW_NUMBER() OVER (Order BY KlantNr) AS RowNumber, KlantNr, Name
FROM tbl_ntorder_Klant)
SELECT *
FROM Klanten
WHERE RowNumber between 21 and 30

This works fine if you want just 10 records, but what i want is 10
records from a filtered table. I want to add a where clause to the
statement so i can say for example "where Name = @Name" so that it
filters the table on records where name = @name FIRST and after that,
return me the records between 21 and 30 because what i get now when i
try to do something like that is it first filters the records between
rowcount 21 and 30 and then looks for records where name = @name which
is not the correct result as the ROW_NUMBER for those records might be
31, 32 etc (and thus don't show up in the result).

Does anybody have an idea how to solve this?

7 Sep 2006 4:14 PM

Aaron Bertrand [SQL Server MVP]

I think you're just putting the where clause in the wrong place. If you
want to filter by name first and then filter by RowNumber, you need to
filter on name before you filter on RowNumber. Did you try any variation on
this?

WITH Klanten AS
(
SELECT ROW_NUMBER() OVER (Order BY KlantNr) AS RowNumber, KlantNr, Name
FROM tbl_ntorder_Klant
WHERE Name = @Name
)
SELECT RowNumber, KlantNr, Name
FROM Klanten
WHERE RowNumber between 21 and 30;

<bert.vanderauw***@gmail.com> wrote in message
Show quote

news:1157645133.299612.100780@i3g2000cwc.googlegroups.com...
> Hi,
>
> I've got the following question. I always used this technique with
> temporary tables to cope with paging in sql server 2000 but now that we
> got sql 2005 running in our company i've got to rewrite the paging
> stored procedures to a version that uses ROW_NUMBER.
>
> Basically, this is the sql statement that i use now to get 10 records
> back:
>
> WITH Klanten AS (
> SELECT ROW_NUMBER() OVER (Order BY KlantNr) AS RowNumber, KlantNr, Name
> FROM tbl_ntorder_Klant)
> SELECT *
> FROM Klanten
> WHERE RowNumber between 21 and 30
>
> This works fine if you want just 10 records, but what i want is 10
> records from a filtered table. I want to add a where clause to the
> statement so i can say for example "where Name = @Name" so that it
> filters the table on records where name = @name FIRST and after that,
> return me the records between 21 and 30 because what i get now when i
> try to do something like that is it first filters the records between
> rowcount 21 and 30 and then looks for records where name = @name which
> is not the correct result as the ROW_NUMBER for those records might be
> 31, 32 etc (and thus don't show up in the result).
>
> Does anybody have an idea how to solve this?
>

7 Sep 2006 4:14 PM

Anith Sen

Have you tried including the predicate in the CTE itself like:

WITH Klanten AS (
    SELECT ROW_NUMBER() OVER ( ORDER BY KlantNr) AS RowNumber,
           KlantNr, Name
      FROM tbl_ntorder_Klant
     WHERE Name = @Name )
SELECT *
FROM Klanten WHERE...

--
Anith

8 Sep 2006 8:00 AM

bert.vanderauwera

Anith Sen schreef:

> Have you tried including the predicate in the CTE itself like:
>
> WITH Klanten AS (
>     SELECT ROW_NUMBER() OVER ( ORDER BY KlantNr) AS RowNumber,
>            KlantNr, Name
>       FROM tbl_ntorder_Klant
>      WHERE Name = @Name )
> SELECT *
>   FROM Klanten WHERE...
>
> --
> Anith

That's what i had initially but due to being at work behind my computer
for too long i didn't see the stupid mistake i made. The webpage that
used the stored procedure kept sending 11 and 20 respectively for
lowerbound and upperbound parameters (hard coded testscenario that i
forgot about) but the filtering never resulted more than 10 records,
there was no ROW_NUMBER between 11 and 20 (only 5 records or something)
and thus no result. I need more coffee.

7 Sep 2006 6:44 PM

Alexander Kuznetsov

In many cases when you filter by ROW_NUMBER(), everything is scanned
and only then the condition on ROW_NUMBER() is applied.
THis is inefficient - what is the point of continuing to select when
ROW_NUMBER() is already above 30?
Use TOP 30 with proper ORDER BY clause in in addition to your criteria
on ROW_NUMBER() - that may be dramatically faster.

7 Sep 2006 7:03 PM

Steve Dassin

"Alexander Kuznetsov" <AK_TIREDOFSPAM@hotmail.COM> wrote in message
news:1157654679.673260.31010@m73g2000cwd.googlegroups.com...
> In many cases when you filter by ROW_NUMBER(), everything is scanned
> and only then the condition on ROW_NUMBER() is applied.
> THis is inefficient - what is the point of continuing to select when
> ROW_NUMBER() is already above 30?
> Use TOP 30 with proper ORDER BY clause in in addition to your criteria
> on ROW_NUMBER() - that may be dramatically faster.

A less than sophisticated concept implemented at a less than sophisticated
level.
At least they are consistent.

http://racster.blogspot.com/2006/09/rac-rank-this.html