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 Mon, 14 Apr 2008 18:51:30 GMT
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

Mime
View raw message