How to select top X from a Row_number in SQL Server ?

Question

I have a data sample, and now i want to get data using TOP X combine ROW_NUMBER()

IndexNo ProductName
1       Black
2       Blue
3       Brown
4       Green
5       Red
6       White
7       Yellow

As follow in this case, i want to get the data, which after run SQL Statement, result as

IndexNo ProductName
3       Brown
4       Green
5       Red

I use this sql statement for this case, but i get this error Invalid column name 'IndexNo' , this is sql statement .

SELECT TOP 3  ROW_NUMBER() OVER(ORDER BY TEMPA.ProductName) AS IndexNo, TEMPA.ProductName  
FROM (
            SELECT DISTINCT ProductName FROM PRODUCTS WHERE ProductType ='Food'
) AS TEMPA
WHERE IndexNo  between 3 and 5

Answer 1

You could use another level of subquery with parentheses.

SELECT TOP 3 * FROM
( SELECT ROW_NUMBER() OVER(ORDER BY TEMPA.ProductName) AS IndexNo, TEMPA.ProductName  
FROM (
            SELECT DISTINCT ProductName FROM PRODUCTS 
) AS TEMPA
  ) as TEMPB
WHERE IndexNo  between 3 and 5

DEMO