db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Daniel Noll <dan...@nuix.com>
Subject Order the results, *and* slice them
Date Fri, 06 Mar 2009 03:09:27 GMT
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

Mime
View raw message