TSQL - Invalid Column Name RowNumber

Question

Why does it say 'Invalid Column Name' for the RowNumber in WHERE.

SELECT Id, Name,
ROW_NUMBER() OVER (ORDER BY Id) AS 'RowNumber'
FROM Folks
WHERE RowNumber=3

While can be used with ORDER BY

SELECT Id, Name
ROW_NUMBER() OVER (ORDER BY Id) AS 'RowNumber'
FROM Folks
ORDER BY RowNumber DESC

Besides the order of operations, you have a typo - missing a comma. — Taryn, Jul 25 '14 at 19:42
I assume it's not duplicate. Accepted Answer indicated the alias was missing. — Jude, Jul 25 '14 at 19:52

score 9 · Accepted Answer · answered Jul 25 '14 at 19:39

9

Try this:

SELECT  *
FROM    (SELECT Id, Name,
                ROW_NUMBER() OVER (ORDER BY Id) AS 'RowNumber'
         FROM    Folks
        ) AS A
WHERE   RowNumber = 3

answered Jul 25 '14 at 19:39

Jesuraja

3,774
4
24
48

Updated the answer... Try now... – Jesuraja Jul 25 '14 at 19:41
2

'AS A' got it done. What's the idea behind? – Jude Jul 25 '14 at 19:43
It is just a alias name... – Jesuraja Jul 25 '14 at 19:49

score 8 · Answer 2 · answered Jul 25 '14 at 19:41

You are missing a comma:

SELECT f.*
FROM (SELECT Id, Name,
---------------------^
             ROW_NUMBER() OVER (ORDER BY Id) AS RowNumber
      FROM Folks f
     ) f
WHERE RowNumber = 3;

Plus you cannot use a column alias in a where clause at the same level. A subquery or CTE solves that problem.

And, it is best not to use single quotes for column aliases. You should use single quotes only for string and date constants. In this case, rownumber doesn't need to be escaped at all.

_you cannot use a column alias in a where clause at the same level_ Thank you, this is one of those dumb things I should have known and forgot. — coderMe, Dec 14 '18 at 15:48

TSQL - Invalid Column Name RowNumber

2 Answers2

Linked

Related