Order the results, *and* slice them

[Daniel Noll <da...@nuix.com>]

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


-- 
Daniel Noll                            Forensic and eDiscovery Software
Senior Developer                              The world's most advanced
Nuix                                                email data analysis
http://nuix.com/                                and eDiscovery software

Re: Order the results, *and* slice them

[Rick Hillegas <Ri...@Sun.COM>]

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
>
>

Re: Order the results, *and* slice them

[Bryan Pendleton <bp...@amberpoint.com>]

> 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?

I think you might be seeing DERBY-3634:
http://issues.apache.org/jira/browse/DERBY-3634

I think there is some renewed work on the ROW_NUMBER feature for Derby 10.5,
so hopefully some of these problems will be addressed soon.

bryan