Row_Number Over Where RowNumber between

Question

I'm try to select a certain rows from my table using the row_number over. However, the sql will prompt the error msg "Invalid column name 'ROWNUMBERS' ". Anyone can correct me?

SELECT ROW_NUMBER() OVER (ORDER BY  Price ASC) AS ROWNUMBERS, * 
FROM Product
WHERE  ROWNUMBERS BETWEEN  @fromCount AND @toCount

Answer 1

Attempting to reference the aliased column in the WHERE clause does not work because of the logical query processing taking place. The WHERE is evaluated before the SELECT clause. Therefore, the column ROWNUMBERS does not exist when WHERE is evaluated.

The correct way to reference the column in this example would be:

SELECT a.*
FROM
    (SELECT ROW_NUMBER() OVER (ORDER BY  Price ASC) AS ROWNUMBERS, * 
    FROM Product) a
WHERE a.ROWNUMBERS BETWEEN @fromCount AND @toCount

For your reference, the order for operations is:

1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY

Answer 2

There is another answer here that solves the specific error reported. However, I also want to address the wider problem. It looks a lot like what you are doing here is paging your results for display. If that is the case, and if you can use Sql Server 2012, there is a better way now. Take a look at OFFSET/FETCH:

SELECT First Name + ' ' + Last Name 
FROM Employees 
ORDER BY First Name 
OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY;

That would show the third page of a query where the page size is 5.