Problem with Paging using ROW_NUMBER()

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Problem with Paging using ROW_NUMBER()

Author

Topic

shawnmolloy
Yak Posting Veteran

93 Posts

Posted - 2007-09-27 : 13:45:52

For some reason this query, when I include this line, does not work. But I comment it out and it works fine - do you see anything wrong that I can't see??

(Comment this out and it works)
AND RowNumber >= @RowStart
AND RowNumber <= @RowEnd

Query:


ALTER PROCEDURE [dbo].[GetNewsItemAbstracts]    
 -- Add the parameters for the stored procedure here    
 @CategoryID int,
 @NewsType char(25),
-- paging
 @PageSize int, @PageNumber int 

AS

Declare @RowStart int 
Declare @RowEnd int 

if @PageNumber > 0 
Begin 

SET @PageNumber = @PageNumber -1 

SET @RowStart = @PageSize * @PageNumber + 1; 
SET @RowEnd = @RowStart + @PageSize - 1 ; 

SELECT 
	RowNumber,[id],[headline],[publishedDate]
FROM
   (SELECT [id],[headline],[publishedDate],[NewsCategoryID], [NewsType],
         ROW_NUMBER() OVER(ORDER BY [publishedDate]) as RowNumber
    FROM NewsItem ni
   ) as PagedNewsItems

WHERE 
	[NewsCategoryID] = @CategoryID 
	AND [NewsType] = @NewsType
	AND RowNumber >= @RowStart 
-	AND RowNumber <= @RowEnd
END

Thank yoU!!

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-09-27 : 14:47:54

You can't just say "it doesn't work"! Does it return an error, wrong results, no records, the computer freezes, your data is deleted, your house catches on fire, etc? ... don't just say "it doesn't work", that tells us nothing.

- Jeff
http://weblogs.sqlteam.com/JeffS

shawnmolloy
Yak Posting Veteran

93 Posts

Posted - 2007-09-27 : 14:58:48

Sorry! It does not return any results when I include those two filters:

AND RowNumber >= @RowStart
AND RowNumber <= @RowEnd

The @PageSize parameter I specify is 10 and the @PageNumber is 1. There are results, so its not like there are no rows. It must be something in my math or something... Please let me know if you need more info.

Thanks.

-- shawn

Kristen
Test

22859 Posts

Posted - 2007-09-27 : 15:00:13

"But I comment it out and it works fine"

Tempts me to reply "Well stick to that then"

But Jeff is right, Need More Data please ...

shawnmolloy
Yak Posting Veteran

93 Posts

Posted - 2007-09-27 : 15:00:46

Not to mention every time I run this query the cat catches on fire and starts dancing the Macarena while singing Britney Spears "Opps I did it again".

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-09-27 : 15:03:47

Can you verify that page size is being set correctly? If that is NULL OR 0 that could have some impact on your query. :)

Perhaps change:

if @PageNumber > 0 
Begin

To:

if (@PageNumber > 0 AND @PageSize > 0)
Begin

???

shawnmolloy
Yak Posting Veteran

93 Posts

Posted - 2007-09-27 : 15:10:25

Lamprey,

Yes - in my above post I say:

quote:
the @PageSize parameter I specify is 10 and the @PageNumber is 1

I should have included that in the original post - sorry.

Kristen
Test

22859 Posts

Posted - 2007-09-27 : 15:15:01

"Not to mention every time I run this query the cat catches on fire and starts dancing the Macarena while singing Britney Spears "Opps I did it again"."

That's SO cool ... well, apart from the choice of song. Replace the Cat with one having better taste, maybe?

Long shot : That single hyphen is being interpreted as a MINUS instead of a broken-comment-out is it?


	AND RowNumber >= @RowStart 
-	AND RowNumber <= @RowEnd

Kristen

shawnmolloy
Yak Posting Veteran

93 Posts

Posted - 2007-09-27 : 15:17:52

Sorry, that single hyphen isn't in the code... the query doesn't error out at all, it just runs and returns no results.

Kristen
Test

22859 Posts

Posted - 2007-09-27 : 15:21:38

"that single hyphen isn't in the code"

As much as I expected, but though it worth checking. Ho! Hum! Back to the drawing board.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-09-27 : 15:33:27

I think ranking functions must be applied within Common Table Expressions for filtering. Try this:


Begin 

SET @PageNumber = @PageNumber -1 

SET @RowStart = @PageSize * @PageNumber + 1; 
SET @RowEnd = @RowStart + @PageSize - 1 ; 

;--  <--Don't forget the semi-colon!
with MyResults as
    (SELECT [id],
	    [headline],
	    [publishedDate],
	    [NewsCategoryID],
	    [NewsType],
	    ROW_NUMBER() OVER(ORDER BY [publishedDate]) as RowNumber
    FROM NewsItem ni
    where [NewsCategoryID] = @CategoryID 
	AND [NewsType] = @NewsType)
select	[id],
	[headline],
	[publishedDate],
	[NewsCategoryID],
	[NewsType],
	RowNumber
from	MyResults
where	RowNumber >= @RowStart 
	AND RowNumber <= @RowEnd

end

e4 d5 xd5 Nf6

shawnmolloy
Yak Posting Veteran

93 Posts

Posted - 2007-09-27 : 15:47:38

Dude! That totally fixed my problem. *High five*! Awesome...

Thanks so much. So two questions... what is that random semi-colon for? It doesn't seem to delimit anything??

Also, what is Common Table Expressions? Is that using the "with" statement? I'll do a search on that and research more. Seems handy if it fixed my problem.

Well, now my cat is still catching on fire, but its vocal range has improved quite a bit and its stepped up to singing Christina Aguilera songs - so I guess that is a plus.

Thanks for your help guys.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-09-27 : 16:02:53

Common Table Expressions are a new feature in 2005. They can be used to reference a recordset more than once, or for filtering on the new ranking functions, or for recursion. They are implemented using the WITH clause.
Unless your CTE is the first statement in your batch, it is absolutely necessary to prefix it with a semi-colon for the simple and obvious reason that if you don't, you will get an error message saying you need to. Good enough for me...

e4 d5 xd5 Nf6

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-09-27 : 16:13:31

Good catch by Blindman. Also, if it matters, you should be able to just modify your original query:

SELECT 
	RowNumber,[id],[headline],[publishedDate]
FROM
   (SELECT [id],[headline],[publishedDate],[NewsCategoryID], [NewsType],
         ROW_NUMBER() OVER(ORDER BY [publishedDate]) as RowNumber
    FROM NewsItem ni
	WHERE [NewsCategoryID] = @CategoryID 
	AND [NewsType] = @NewsType
   ) as PagedNewsItems

WHERE RowNumber >= @RowStart 
	AND RowNumber <= @RowEnd
END

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-09-27 : 16:53:18

Shawn, see if Lamprey's code works. If so, then I was wrong about CTEs being required for filtering ranking functions.

e4 d5 xd5 Nf6

shawnmolloy
Yak Posting Veteran

93 Posts

Posted - 2007-09-27 : 17:17:14

Yeah, actually Lampray's code did work as well.

Thank you.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-09-27 : 17:45:49

Mine is superior, because it has a higher obfuscatory factor. That means you get paid more to write it or debug it.

e4 d5 xd5 Nf6

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-09-27 : 18:12:20

Touche! Income by obfuscation, brilliant! :)

shawnmolloy
Yak Posting Veteran

93 Posts

Posted - 2007-09-27 : 18:31:21

LoL good point - when I get my code review it will make me look that much smarter.

By the way - is "obfuscatory" a word? I think it ranks right up there with "consistentization" and "terceryistic".

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-09-28 : 09:36:21

You can look up "obfuscatory" on the internet here:
http://dictionary.reference.com/browse/obfuscatory
The working definition of "obfuscatory" is "Something you have to look up on the internet."

e4 d5 xd5 Nf6

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources