db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Dag.Wan...@Sun.COM (Dag H. Wanvik)
Subject Re: [jira] Commented: (DERBY-3634) Cannot use row_number() in ORDER BY clause
Date Tue, 29 Sep 2009 18:55:21 GMT
"Micky Li (JIRA)" <jira@apache.org> writes:

> After apply the derby-3634-c.diff patch, it compiles and jar OK. The
> SQL query string as I mentioned at 28/Sep/09 09:02 PM works well.
> My target is to use ROW_NUMBER() with ORDER BY in one SQL query, but
> there shows a error as following: 'Syntax error: Encountered "ORDER"
> at line 1, column 113'
> the SQL query string is
> 
> SELECT * FROM ( SELECT ROW_NUMBER() OVER() AS rownum, event_log.* FROM event_log WHERE
event_log.id<=2147483647 ORDER BY id DESC) AS TR WHERE rownum >= 2189 AND rownum <=
2288

The problem here is that you have an ORDER BY in a sub-select, which
is currently not allowed by Derby. ORDER BY is only allowed on the
outer SELECT. This patch does allow you to use ROW_NUMBER() in the
order by clause, but this is not what you want, it seems.

E.g. SELECT ... ORDER BY ROW_NUMBER() OVER () 
is allowed by the patch

I plan to work on relaxing this restriction as part of the 10.6 work.

You could probably achieve what you want by using the new OFFSET/FETCH
FIRST syntax introduced in 10.5, cf.

http://db.apache.org/derby/docs/10.5/ref/rrefsqljoffsetfetch.html

Dag


>
> It clearly that the syntax error is caused by the ORDER which is
> added. But I think the derby-3634-c.diff patch is to solve this
> problem, am I right?
>
> Is there something I missed?

Mime
View raw message