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