Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Problems using SQL 2005's ROW_NUMBER() function 1

Status
Not open for further replies.
Jun 9, 2006
159
US
Why does this query return an error saying:

"Invalid column name 'rowNum'."

Code:
SELECT 
	ROW_NUMBER() OVER (ORDER BY userid) as rowNum, 
	userid, 
	dateadded
FROM 
	Wisetopic_Friend
WHERE
	rowNum > 3


Shawn Molloy
Seattle, WA
 
rowNum does not exist until the query is finished.

You will have to select your result set into a temp table, then select from that using your WHERE clause.

Jim
 
here are 2 ways, run this in Adventureworks
Code:
SELECT * FROM ( SELECT 
    ROW_NUMBER() OVER (ORDER BY addressline1) as rowNum, 
     addressline1, 
    city
FROM  person.address) as x 
where rowNum > 3

Code:
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

Denis The SQL Menace
SQL blog:
 
Just Curious - Why the need to assign an alias to the sub query if its never referenced. For example this works:

Code:
	SELECT * FROM 
    (SELECT ROW_NUMBER() OVER (ORDER BY A.UserId) AS rowNum,
	A.*, B.UserName
	FROM WiseTopic_Friend A
	INNER JOIN WiseTopic_User B 
	ON A.UserId = B.UserId) AS X
	WHERE rowNum between 1 and 12

but when I reomve the AS X it throws an error:

Code:
	SELECT * FROM 
    (SELECT ROW_NUMBER() OVER (ORDER BY A.UserId) AS rowNum,
	A.*, B.UserName
	FROM WiseTopic_Friend A
	INNER JOIN WiseTopic_User B 
	ON A.UserId = B.UserId)
	WHERE rowNum between 1 and 12

"Incorrect syntax near the keyword 'WHERE'."

Shawn Molloy
Seattle, WA
 
I belive because the subquery generates a table, and needs an alias.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top