select range with row number

Question

I need data from a SQL Server 2008 table with a serial number (Row number) in reverse order (desc).

My table name is ProductDetails.

I use this query

WITH Sum_Ctl (TOt) as 
(
    select count(*)+1 
    from ProductDetails 
    where ProductDetails.UserId = 38 
)
SELECT 
    Tot - ROW_NUMBER() OVER (ORDER BY PrdID asc) AS SNO,
    * 
FROM
    ProductDetails, Sum_Ctl 
WHERE
    UserId = 38 

2192 rows of data exist in the table for user 38. And I get it using the above query

SNO  column 2192  2191  2190  .... to  1

The problem : I need 20 rows of data, ie 2020 to 2000 only

I tried and SNO between 2020 and 2000 with the query but got

Invalid column name 'SNO'.

Then I tried

where 
    UserId = 38  
    and Tot - ROW_NUMBER() OVER (ORDER BY PrdID asc) between 2020 and 2000

But

Windowed functions can only appear in the SELECT or ORDER BY clauses.

Any help would be appreciated

Answer 1

There problem here is you cannot use windowed functions in the where clause. This is a consequence of the logical processing order. In short the row number is generated after everything else has been processed, including any filters you may have in your where.

You can work around this restriction using subqueries or CTEs. In this example the ROW_NUMBER is calculated in a subquery. This in turn is filtered by the main query.

Example

SELECT
    *
FROM
    (
        -- SNO will be available to outer WHERE.
        SELECT
            ROW_NUMBER() OVER (ORDER BY PrdID ASC) AS sno,
            *
        FROM
            ProductDetails 
        WHERE
            ProductDetails.UserId = 38 
    ) AS sq
WHERE
    sno BETWEEN 2000 AND 2020
;

This works because the subquery is processed in its entirety, before the results are passed to the outer query.