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