Knut Anders Hatlen <[EMAIL PROTECTED]> writes:
> Knut Anders Hatlen <[EMAIL PROTECTED]> writes:
>
>> caxo <[EMAIL PROTECTED]> writes:
>>
>>> I have to take a certain number of rows ordered by a field.
>>> And the derby say me error: Syntax error: Encountered "ORDER"
>>>
>>> SELECT * FROM (
>>> SELECT ROW_NUMBER() OVER () AS R,
>>> playerId,
>>> playerName,
>>> points
>>> FROM ranks
>>> ORDER BY points DESC
>>> )
>>> AS TR WHERE R <= 10
>>>
>>> This is my query and I'm not shure is it a bug or I'm not doing something
>>> right.
>>
>> It should work if you move the ORDER BY clause into the outer SELECT,
>> like this:
>>
>> SELECT * FROM (
>> SELECT ROW_NUMBER() OVER () AS R,
>> playerId,
>> playerName,
>> points
>> FROM ranks) AS TR
>> WHERE R <= 10 ORDER BY points DESC
>
> Oops, sorry that won't work... At least, it won't give you the results
> you want.
This should work, though (but I'm not sure if the optimizer is able to
make it very efficient):
SELECT * FROM
(SELECT COUNT(r2.playerid)+1 rank, r1.playerid, r1.playername, r1.points
FROM ranks r1 LEFT OUTER JOIN ranks r2 ON r1.points < r2.points
GROUP BY r1.playerid, r1.playername, r1.points) s
WHERE RANK <= 10 ORDER BY rank;
--
Knut Anders
