SQL ROW_NUMBER() issue with paging

database, row-number, sql, sql-server, t-sql

Solution

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

Problem

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 ```

Original source