views:

582

answers:

4

Hi all,

I ve got the following query which is throwing the following error

Unkown Column 'RowNum'

WITH Employees AS
(
SELECT
   (keyTblSp.RANK * 3) AS [Rank],
    sp.*,
    addr.Street,
    addr.PostOfficeBox,
    addr.StreetNumber
FROM Employee sp    
    INNER JOIN 
        FREETEXTTABLE(Employee, *, 'something', 1000) AS keyTblSp
        ON sp.EmployeeId = keyTblSp.[KEY]    
    LEFT OUTER JOIN [Address] addr ON addr.EmployeeId = sp.EmployeeId 
UNION ALL
SELECT
    (keyTblAddr.RANK * 2) AS [Rank],
    sp.*,
    addr.Street,
    addr.PostOfficeBox,
    addr.StreetNumber
FROM Employee sp    
    LEFT OUTER JOIN [Address] addr ON addr.EmployeeId = sp.EmployeeId 
    INNER JOIN 
        FREETEXTTABLE([Address], *, 'something', 1000) AS keyTblAddr
        ON addr.AddressId = keyTblAddr.[KEY]
)

SELECT ROW_NUMBER() OVER (ORDER BY [Rank] DESC) AS RowNum, *
FROM Employees
WHERE RowNum BETWEEN (1 - 1) * 10 + 1 AND 1 * 10
ORDER BY Rank DESC
+4  A: 

This is because aliases are not recognized in WHERE clauses. Instead, use the full query like this:

WHERE ROW_NUMBER() OVER (ORDER BY [Rank] DESC) BETWEEN (1 - 1) * 10 + 1 AND 1 * 10

Crimson
+1. Spot on. Gr8.
Guru
A: 

How about:

select top 10 * from Employees order by Rank Desc

Alternatively, does it work without the where rownum clause. (why is your between so tricky?).

Oliver Townshend
+1  A: 

Your WHERE clause cannot refer to a window or aggregate function like ROW_NUMBER(). If you want to filter on the result of ROW_NUMBER(), you need to do so in the HAVING clause:

...
SELECT ROW_NUMBER() OVER (ORDER BY [Rank] DESC) AS RowNum, *
FROM Employees
HAVING RowNum BETWEEN (1 - 1) * 10 + 1 AND 1 * 10    
ORDER BY Rank DESC    
Roland Bouman
+2  A: 

Try wrpping up your query to get the name usable in the where clause

SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY [Rank] DESC) AS RowNum , * FROM Employees) AS Results WHERE RowNum BETWEEN (1 - 1) * 10 + 1 AND 1 * 10 ORDER BY Rank

Robert