October 5, 2005 at 12:00 pm
Does anyone know where i can find some documentation regarding the new Ranking Functions in sql2005 specifically regarding the fact that "SQL 2005 will not allow any reference to ranking function in the where clause"? I know this is NOT allow in 2005:
SELECT RANK() OVER(ORDER BY UserId) AS RowNum, UserId, UserName, LastLogin
FROM tblUsers
WHERE RowNum BETWEEN 100 AND 200
But I can do this:
SELECT (
FROM
(
SELECT RANK() OVER(ORDER BY UserId) AS rownum, userid, username, lastlogin
FROM tblUsers
) AS D
WHERE RowNum BETWEEN 100 AND 200
TIA.
October 5, 2005 at 12:07 pm
This is just an assumption, but I think the the result set must be created first for the RANK() function to be able to be executed. A little like when you can't do select 1 as test from table where test = 1 (can't use the alias in the where condition, but you can once it's in a derived table).
October 5, 2005 at 12:08 pm
Or maybe this is the error message you are getting!!!
What happens if you move the rank() function in the where instead of the alias name (assuming it's even possible)?
October 5, 2005 at 12:22 pm
Then I get this error:
Msg 4108, Level 15, State 1, Line 1
Windowed functions can only appear in the select or order by clauses.
October 5, 2005 at 12:27 pm
Then I'd go back to my original assumption... Check out the 2k5 books online, maybe they have the explaination.
October 5, 2005 at 1:35 pm
Remi is correct, and it is all in BOL. Start at http://msdn2.microsoft.com/en-us/library/ms245878
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply