sql for limit record group by in derby database

Question

I want to retrieve ten records only in derby database. I have been searching the tutorial for doing that:

select * from (select  ROW_NUMBER() OVER() AS rownum,name,effort from (select  name, effort from
(select TBL_PROJECT_DETAIL.P_NAME as Name, sum(TBL_TIMESHEET.EFFORT) as effort from
TBL_TIMESHEET join tbl_project_detail on TBL_TIMESHEET.PROJECT_ID = TBL_PROJECT_DETAIL.ID group by TBL_PROJECT_DETAIL.P_NAME) as New)
as new2)
as new3 
where rownum <= 10

I can limit the data to 10 first by using where rownum <= 10 at the end, but the problem is that data is not ordered using order by clause first so I got 10 random data.

When I put order by before the data give rownum:

select * from (select  ROW_NUMBER() OVER() AS rownum,name,effort from 
(select  name, effort from
(select TBL_PROJECT_DETAIL.P_NAME as Name, sum(TBL_TIMESHEET.EFFORT) as effort from
TBL_TIMESHEET join tbl_project_detail on TBL_TIMESHEET.PROJECT_ID = TBL_PROJECT_DETAIL.ID group by TBL_PROJECT_DETAIL.P_NAME)
as New)
as new2 order by effot desc)
as new3  where rownum <=10

It gave error. Its like cannot group by when you put the result as inner table for query again, but the requirement for derby is to use row_number. Is first add row_number in inner and use that in where, in next layer. Any one can help? Derby is not support limit. Any other way that I can have 10 record first that I order by ?

Answer 1

i have been researching and i found like this to limit in derby by using

FETCH FIRST 10 ROWS ONLY

but poor me that support in derby 10.5 and i'm using derby 10.4 bundle with glassfish 2.1 any one maybe can help with this if i still using 10.4 cuz i'm too afraid for migrated

Answer 2

You can give the order by clause inside "OVER() "

OVER(order by effot desc) AS rownum