Using ROW_NUMBER in a where clause
It really bugs me to have to write too much code. Thats
whay whan I want to find the 2nd page of rows it really annoys me that I have to
use a derived table to do this.
WITH C AS
(
SELECT ROW_NUMBER() OVER(PARTITION BY col1 ORDER BY col2)
AS rownum,
col1, col2, col3
FROM
dbo.T1
)
SELECT col1, col2, col3
FROM C
WHERE rownum BETWEEN 11 AND
20;
Why can't I just do
SELECT col1, col2, col3
FROM dbo.T1
WHERE ROW_NUMBER() OVER(PARTITION
BY col1 ORDER BY col2) BETWEEN 11 AND 20;
Boy thats a lot simpler.
Well Itzik has posted a connect item for exactly this.
https://connect.microsoft.com/SQLServer/feedback/details/532474/qualify-request-for-a-new-filtering-clause?wa=wsignin1.0
If you think it would be a good idea then please vote.