Problems using SQL 2005's ROW_NUMBER() function 1

[ShawnMolloy]

Why does this query return an error saying:

"Invalid column name 'rowNum'."

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

Shawn Molloy
Seattle, WA

 

[jbenson001]

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

 

[SQLDenis]

one way

SELECT * FROM (SELECT 
    ROW_NUMBER() OVER (ORDER BY userid) as rowNum, 
    userid, 
    dateadded
FROM 
    Wisetopic_Friend) x
where x.rowNum > 3

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[jbenson001]

Ahhh very cool Denis...

 

[SQLDenis]

here are 2 ways, run this in Adventureworks

SELECT * FROM ( SELECT 
    ROW_NUMBER() OVER (ORDER BY addressline1) as rowNum, 
     addressline1, 
    city
FROM  person.address) as x 
where rowNum > 3

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:

http://sqlservercode.blogspot.com/

 

[ShawnMolloy]

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

	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:

	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

 

[jbenson001]

I belive because the subquery generates a table, and needs an alias.

 

[SQLDenis]

because you need to alias the derived table

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/