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
