Problems With ROW_NUMBER() In A WHERE Clause

This is a question that came up today. A person had a query like this


USE AdventureWorks
GO

SELECT
ROW_NUMBER() OVER (ORDER BY addressline1) AS rowNum,
addressline1,
city
FROM person.address
WHERE rowNum > 3;

That gave the following error
Server: Msg 207, Level 16, State 1, Line 6
Invalid column name 'rowNum'.

What you have to do is use Common Table Expressions or use a subquery. Below are both methods.

--Subquery
SELECT * FROM ( SELECT
ROW_NUMBER() OVER (ORDER BY addressline1) AS rowNum,
addressline1,
city
FROM person.address) AS x
WHERE rowNum > 3;


--CTE
WITH x (rowNum,addressline1,
city) AS
(SELECT
ROW_NUMBER() OVER (ORDER BY addressline1) AS rowNum,
addressline1,
city
FROM person.address)

SELECT * FROM X
WHERE rowNum > 3;