db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Thomas Nielsen <Thomas.Niel...@Sun.COM>
Subject Re: Problem with derby 10.4
Date Tue, 15 Apr 2008 07:24:27 GMT
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

Mime
View raw message