Why do I get an error when using ROW_NUMBER in SQL Server?

Question

I have written the following SQL code to run on my SQL Server.

SELECT 
   atd.DeviceID, 
   ROW_NUMBER() over(order by atd.deviceid) as rownumber
FROM 
   dbo.Devices atd  
WHERE 
  (rownumber between 11 and 20);

I get the following output:

Msg 207, Level 16, State 1, Line 5
Invalid column name 'rownumber'.
Msg 207, Level 16, State 1, Line 5
Invalid column name 'rownumber'.

As you can see, I want to use ROW_NUMBER to get only a subset of the rows that would normally be returned by a query. I've never used the ROW_NUMBER function before.

What am I doing wrong?

Answer 1

You can't use the alias of your column directly, wrap it on a derived table or a CTE:

SELECT *
FROM (  SELECT DeviceID, 
        ROW_NUMBER() over(order by deviceid) as rownumber
        FROM dbo.Devices) atd 
WHERE (rownumber between 11 and 20);

Or

;WITH CTE AS
(
    SELECT DeviceID, 
    ROW_NUMBER() over(order by deviceid) as rownumber
    FROM dbo.Devices
)
SELECT *
FROM CTE 
WHERE (rownumber between 11 and 20);