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
