locked
invalid column name? RRS feed

  • 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?
    Monday, August 31, 2009 4:20 PM

Answers

  • 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
    • Proposed as answer by HunchbackMVP Monday, August 31, 2009 4:36 PM
    • Marked as answer by c0pe Monday, August 31, 2009 6:31 PM
    Monday, August 31, 2009 4:31 PM

All replies

  • 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
    • Proposed as answer by HunchbackMVP Monday, August 31, 2009 4:36 PM
    • Marked as answer by c0pe Monday, August 31, 2009 6:31 PM
    Monday, August 31, 2009 4:31 PM
  • 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

    Monday, August 31, 2009 6:12 PM
  • thanks
    Monday, August 31, 2009 6:31 PM