db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Knut Anders Hatlen <Knut.Hat...@Sun.COM>
Subject Re: Problem with derby 10.4
Date Tue, 15 Apr 2008 07:55:16 GMT
Knut Anders Hatlen <Knut.Hatlen@Sun.COM> writes:

> Knut Anders Hatlen <Knut.Hatlen@Sun.COM> writes:
>
>> caxo <caxo@imperiaonline.org> 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

Mime
View raw message