Where conditions on the generated row number generated by ROW_NUMBER() OVER() does not work for lower bounds > 0.
Assuming 5 records matching the SELECT criteria...
SELECT * FROM
SELECT ROW_NUMBER() OVER() as row_num, inner.* FROM
) AS inner
) AS foo
WHERE row_num > 0 and row_num <= 5;
returns 5 records. 5 records were expected.
Changing the WHERE condition to
WHERE row_num > 2 and row_num <= 4
returns 0 records. 2 records (row_number 3 and 4) were expected.
Log in to post a comment.