You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by caxo <ca...@imperiaonline.org> on 2008/04/14 11:39:06 UTC

Problem with derby 10.4

Hello,

I try use Derby 10.4
And have used the ROW_NUMBER function to limit the result

I used as in manual 

SELECT * FROM (
SELECT
ROW_NUMBER() OVER () AS R,
T.*
FROM T
) AS TR
WHERE R <= 10;

Modified to my need.

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.
Please can you help me?
thanks in front


Re: Problem with derby 10.4

Posted by Thomas Nielsen <Th...@Sun.COM>.
Knut Anders Hatlen wrote:
> 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

I don't think you have any guarantee that you get what you'd expect with 
this approach. The ordering is applied *after* the restriction on the 
subquery select:

ij>
select * from t;
PLAYERNAME               |SCORE
-------------------------------------
Emmet Smith              |384
Joe Montana              |497
Jerry Rice               |1045

3 rows selected
ij>
select * from (select t.*, row_number() over () as r from t) as tr where 
r <= 2;
PLAYERNAME               |SCORE      |R
----------------------------------------------------------
Emmet Smith              |384        |1
Joe Montana              |497        |2

2 rows selected
ij>
select * from (select t.*, row_number() over () as r from t) as tr where 
r <= 2 order by score desc;
PLAYERNAME               |SCORE      |R
----------------------------------------------------------
Joe Montana              |497        |2
Emmet Smith              |384        |1

2 rows selected
ij>


...effectively ignoring the third, and best scoring, player. That wasn't 
what you really intended with your query.

That said, it *might* be possible to cheat derby into giving you the 
rows ordered by score by using a index on the score column, and 
tailoring your query so that the optimizer chooses that index for the 
subquery select row retrieval, effectively ordering the result for you?
There's still no guarantee the optimizer *will* use the index though.

Cheers,
Thomas
-- 
Thomas Nielsen

Re: Problem with derby 10.4

Posted by Knut Anders Hatlen <Kn...@Sun.COM>.
Knut Anders Hatlen <Kn...@Sun.COM> writes:

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

Re: Problem with derby 10.4

Posted by Knut Anders Hatlen <Kn...@Sun.COM>.
Knut Anders Hatlen <Kn...@Sun.COM> writes:

> caxo <ca...@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.

-- 
Knut Anders

Re: Problem with derby 10.4

Posted by Knut Anders Hatlen <Kn...@Sun.COM>.
caxo <ca...@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

-- 
Knut Anders

Re: Problem with derby 10.4

Posted by Thomas Nielsen <Th...@Sun.COM>.
caxo wrote:
> 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

I'm sorry to say that Derby does not support ORDER BY in a subquery like 
you are trying to do with your query - seems its actually not allowed in 
the SQL standard.

Derby 10.4 is the first version that support (some of the) window 
functions, so the features are still a little limited. It does not (yet) 
support ordering on the window clause  (" ... OVER ( ORDER BY 
ranks.points DESC) AS R ... ") which would be the standardized way to 
rephrase your query.

Just to leave no stone unturned, I tried creating a view with a order by 
clause but that doesn't seem to work. Didn't check the standard on this 
though - might not be allowed either.

Your only option seems to be to do this in the application as of 10.4.

BR,
Thomas