invalid column name?

Question

Hello,

select ROW_NUMBER() over (order by cost) as RowNum, PKID, Product, Price, Cost
from tblProducts as t
where t.RowNum < 3

I'm getting invalid column name for 'RowNum'? Within the query analyzer when I type 't.' I get the intellisense dropdown for all the columns in the tblProducts table, but no RowNum... when I run this query I get the 'invalid column name' error. This is a syntactical question, the actuall content of this where clause is meaningless... just want to know why I can't use 't.RowNum' here?

Answer 1

Since the SELECT list is processed after the WHERE clause, the RowNum column expression is not accessible in WHERE. You can use a derived table or a CTE:

SELECT RowNum, PKID, Product, Price, Cost
FROM (
SELECT ROW_NUMBER() OVER(ORDER BY cost) AS RowNum, PKID, Product, Price, Cost
FROM tblProducts) AS T
WHERE t.RowNum < 3;


WITH RankedCTE AS (
SELECT ROW_NUMBER() OVER(ORDER BY cost) AS RowNum, PKID, Product, Price, Cost
FROM tblProducts)
SELECT RowNum, PKID, Product, Price, Cost
FROM RankedCTE
WHERE RowNum < 3;

---

Plamen Ratchev

Answer 2

Why are you using row number function you can achieve this by using top key word

select top 3 PKID, Product, Price, Cost from tblProducts 
order by cost

Answer 3

thanks