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
