db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <Richard.Hille...@Sun.COM>
Subject Re: Order the results, *and* slice them
Date Fri, 06 Mar 2009 14:37:52 GMT
Hi Daniel,

As Bryan said, some work is being done to clean up the row_number 
syntax. In addition, it looks like the simpler, alternative FETCH/OFFSET 
syntax will probably make it into 10.5: 
https://issues.apache.org/jira/browse/DERBY-4079

Regards,
-Rick

Daniel Noll wrote:
> Hi all.
>
> I'm trying to use ROW_NUMBER() in conjunction with ORDER BY and not 
> having a lot of luck.
>
> This is the query I'm using, which I found on the Derby wiki:
>
>   SELECT * FROM (
>     SELECT ROW_NUMBER() OVER(ORDER BY startdate ASC) AS row,
>            id, startdate, enddate, ...
>     FROM HistoryRecord
>   ) AS tmp
>   WHERE row >= ? AND row < ?
>
> I have also tried what I consider to be the intuitive way:
>
>   SELECT * FROM (
>     SELECT ROW_NUMBER() OVER() AS row, id, startdate, enddate, ...
>     FROM HistoryRecord
>     ORDER BY startdate ASC
>   ) AS tmp
>   WHERE row >= ? AND row < ?
>
> Both of these give an error at ORDER BY.  Am I doing something wrong, 
> or is this a missing part of the ROW_NUMBER() feature?
>
> Daniel
>
>


Mime
View raw message