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?

Lamak · Accepted Answer · 2012-05-31 21:39:43Z

14

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);

edited May 31, 2012 at 21:39

answered May 31, 2012 at 20:49

Lamak

70.8k12 gold badges119 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Vivian River Over a year ago

Running the first query given in your answer results in the following error: The multi-part identifier "atd.DeviceID" could not be bound.

Aaron Bertrand Over a year ago

@Daniel Just take the atd prefix off the DeviceID column.

Lamak Over a year ago

Downvoter, was the downvote only because of the prefix on the column?

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related