Help Understanding Row_Number

Question

Help Understanding Row_Number

david.c.holley

SSCrazy

Points: 2423
More actions
July 13, 2010 at 6:54 pm

#226818

Can someone explain why when I add the WHERE statement, SQL Server returns the error 'Invalid column name Rn'? The first query works and I can see the result, but obviously somethings going on where SQL Server doesn't recognize it for the WHERE.
I've seen multiple examples similar to this that do appear to work and the syntax appears correct (http://msdn.microsoft.com/en-us/library/ms186734.aspx).
What am I missing?
SELECT *,ROW_NUMBER() OVER (PARTITION BY Comments.ShowNumber ORDER BY Comments.ShowNumber, Comments.Id) as Rn
FROM Comments
SELECT *,ROW_NUMBER() OVER (PARTITION BY Comments.ShowNumber ORDER BY Comments.ShowNumber, Comments.Id) as Rn
FROM Comments
WHERE Rn = 1

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 1

david.c.holley (7/13/2010)
Can someone explain why when I add the WHERE statement, SQL Server returns the error 'Invalid column name Rn'? The first query works and I can see the result, but obviously somethings going on where SQL Server doesn't recognize it for the WHERE.
I've seen multiple examples similar to this that do appear to work. What am I missing?
SELECT *,ROW_NUMBER() OVER (PARTITION BY Comments.ShowNumber ORDER BY Comments.ShowNumber, Comments.Id) as Rn
FROM Comments
SELECT *,ROW_NUMBER() OVER (PARTITION BY Comments.ShowNumber ORDER BY Comments.ShowNumber, Comments.Id) as Rn
FROM Comments
WHERE Rn = 1

Try this :

WITH CTE AS

(

SELECT *,ROW_NUMBER() OVER (PARTITION BY Comments.ShowNumber ORDER BY Comments.ShowNumber, Comments.Id) as Rn

FROM Comments

)

select * from CTE WHERE Rn = 1

SQL Server assign the "RN" to the ROW_NUMBER only during execution.. so ur column name RN wont be present for the SQL server to use from the "FROM" clause..

RakeshRSingh SSCarpal Tunnel Points: 4109 More actions · Answer 2

As per the execution of the SQL Statements that you have provided,

1. First the From clause will be processed

2. Then WHERE clause will be processed

3. Then SELECT clause will be processed

Since the SELECT clause is processed at last, any computation that is processed after WHERE clause cannot be referred before. This is the reason why your ROW_NUMBER computation is not available in WHERE clause processing.

For more information you can refer to T-SQL Querying book by Itzik-ben-gen.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

RakeshRSingh (7/13/2010)
As per the execution of the SQL Statements that you have provided,
1. First the From clause will be processed
2. Then WHERE clause will be processed
3. Then SELECT clause will be processed
Since the SELECT clause is processed at last, any computation that is processed after WHERE clause cannot be referred before. This is the reason why your ROW_NUMBER computation is not available in WHERE clause processing.
For more information you can refer to T-SQL Querying book by Itzik-ben-gen.

Great explanation Rakesh. Pretty much spot on. My only objection would be that that's a very thick, fairly expensive book. You could at least provide a chapter number or page number. Since the book is also expensive, how about a suggestion as to what to look for in the FREE Books Online that comes with SQL Server? 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

david.c.holley SSCrazy Points: 2423 More actions · Answer 4

RakeshRSingh (7/13/2010)
As per the execution of the SQL Statements that you have provided,
1. First the From clause will be processed
2. Then WHERE clause will be processed
3. Then SELECT clause will be processed
Since the SELECT clause is processed at last, any computation that is processed after WHERE clause cannot be referred before. This is the reason why your ROW_NUMBER computation is not available in WHERE clause processing.
For more information you can refer to T-SQL Querying book by Itzik-ben-gen.

Which now that I read that and look back here, that makes sense with the examples provided. http://msdn.microsoft.com/en-us/library/ms186734.aspx

However, I would swear up and down that I've seen example where the value of the Row_Number is referenced in the WHERE statement without using WITH() AS.

Ben Teraberry SSChampion Points: 11845 More actions · Answer 5

david.c.holley (7/14/2010)
However, I would swear up and down that I've seen example where the value of the Row_Number is referenced in the WHERE statement without using WITH() AS.

It's possible you could have seen it like this:

SELECT * FROM

(

SELECT *,ROW_NUMBER() OVER (PARTITION BY Comments.ShowNumber ORDER BY Comments.ShowNumber, Comments.Id) as Rn

FROM Comments

) sq

WHERE Rn = 1

(This is functionally exactly the same as the CTE structure that ColdCoffee provided above.)

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

david.c.holley SSCrazy Points: 2423 More actions · Answer 6

That's entirely a possibility.

It does also solve another question that I had that I've seen crop up here and there about selecting the oldest or youngest child record for a parent. I had run across an example and only remembered that Row_Number() was a part of the statement.