CODEWITHSUNDEEP
sqlsql-serverdatabaset-sqlrow-number
1110
1110

Reputation: 6829

SQL ROW_NUMBER() issue with paging

I use ROW_NUMBER() function to get 50 by 50 items from database table.
@From parameter is row from which to start grabbing 50 rows. (First time it is 1 that 51,101,151 etc.)
I pass parameter @CityId to stored procedure and if first 60 rows in database are cityId=1 and cityId = 2 is in row 61 this stored procedure doesn't return result.
But if I pass @From parameter 51 than it return me result. What I did wrong here?

SELECT  RowConstrainedResult.*
FROM    ( SELECT    ROW_NUMBER() OVER 
( ORDER BY f.ItemCreatedOnDate DESC ) AS RowNum, 
f.*
FROM (
SELECT
      t.ItemIdId,
      t.ItemTypeId,
      t.CreatedOnDate as ItemCreatedOnDate,
      t.CityId as CityId
FROM   dbo.Items    as t
) f) AS RowConstrainedResult
WHERE   RowNum >= @From
    AND RowNum < @From + 50
    AND CityId = @CityId

Upvotes: 2

Views: 3600

Answers (2)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

In your version, ROW_NUMBER() is enumerating all rows. Move the predicate for cityid to the innermost select and ROW_NUMBER() will only enumerate the rows where cityid = 2.

SELECT RowConstrainedResult.*
FROM (
     SELECT ROW_NUMBER() OVER (ORDER BY f.ItemCreatedOnDate DESC) AS RowNum, 
            f.*
     FROM (
          SELECT t.ItemIdId,
                 t.ItemTypeId,
                 t.CreatedOnDate as ItemCreatedOnDate,
                 t.CityId as CityId
          FROM dbo.Items AS t
          WHERE CityId = @CityId
          ) AS f
     ) AS RowConstrainedResult
WHERE RowNum >= @From AND 
      RowNum < @From + 50

Upvotes: 7

jon Z
jon Z

Reputation: 16626

I guess what you want to do is filter for CityId in the inner select query, like this:

SELECT  RowConstrainedResult.*
FROM    ( SELECT    ROW_NUMBER() OVER 
( ORDER BY f.ItemCreatedOnDate DESC ) AS RowNum, 
f.*
FROM (
SELECT
      t.ItemIdId,
      t.ItemTypeId,
      t.CreatedOnDate as ItemCreatedOnDate,
      t.CityId as CityId
FROM   dbo.Items    as t
WHERE CityId = @CityId
) f) AS RowConstrainedResult
WHERE   RowNum >= @From
    AND RowNum < @From + 50

Upvotes: 2

Related Questions