CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqlstored-procedures
1110
1110

Reputation: 6829

ROW_NUMBER() doesn't return good number of items first time

I am getting from database list of items ordered by descending date.
I use ROW_NUMBER() function to get 50 by 50 items.
First time I need to get items from 1-50 than 51-100 etc.
I have in database items with type 1 and those are items that I get.
There is thousands of those items in database.
So when I first time call stored procedure I pass it number one (number from which item to get next 50).
So this should return me first 50 items. Now I add to where clause RowNum < 1+50 but row numbers that I get from query are: 35,37,38,43,44,45,55,67 etc. so my stored procedure returns me only items from 35 to 45. I think I make some stupid miss in my query but can't find it.

SELECT  *
FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY Products.CreatedOnDate DESC ) AS RowNum, Products.*          
          FROM      Products          
          WHERE     Products.CreatedOnDate <= GETDATE()
        ) AS RowConstrainedResult
WHERE   RowNum >= 1
    AND RowNum < 1+50 -- here I use parameter but currently I hardcode '1'
    AND ProductTypeId = 1
ORDER BY RowNum

Upvotes: 1

Views: 168

Answers (1)

Tim Schmelter
Tim Schmelter

Reputation: 460158

You are filtering on ProductTypeID but the inner query with the Row_Number does not:

SELECT  *
FROM    ( SELECT   ROW_NUMBER() OVER ( ORDER BY Products.CreatedOnDate DESC ) AS RowNum
                 , Products.*          
          FROM     Products          
          WHERE    Products.CreatedOnDate <= GETDATE()
          AND      ProductTypeId = 1
        ) AS RowConstrainedResult
WHERE   RowNum >= 1
AND     RowNum < 1+50 
ORDER BY RowNum

Upvotes: 6

Related Questions