JavaDB: get ordered records in the subquery

I have the following "COMPANIES_BY_NEWS_REPUTATION" in my JavaDB database (this is some random data just to represent the structure)

      COMPANY         |  NEWS_HASH       | REPUTATION     | DATE
-------------------------------------------------------------------
      Company A       |   14676757       | 0.12345        | 2011-05-19 15:43:28.0                         
      Company B       |   454564556      | 0.78956        | 2011-05-24 18:44:28.0
      Company C       |   454564556      | 0.78956        | 2011-05-24 18:44:28.0
      Company A       |   -7874564       | 0.123开发者_Go百科45        | 2011-05-19 15:43:28.0 

One news_hash may relate to several companies while a company can relate to several news_hashes as well. Reputation and date are bound to the news_hash.

What I need to do is calculate the average reputation of last 5 news for every company. In order to do that I somehow feel that I need to user 'order by' and 'offset' in a subquery as shown in the code below.

select COMPANY, avg(REPUTATION) from 
   (select * from COMPANY_BY_NEWS_REPUTATION order by "DATE" desc
   offset 0 rows fetch next 5 row only) as TR group by COMPANY;

However, JavaDB allows neither ORDER BY, nor OFFSET in a subquery. Could anyone suggest a working solution for my problem please?

Which version of JavaDB are you using? According to the chapter TableSubquery in the JavaDB documentation, table subqueries do support order by and fetch next, at least in version 10.6.2.1.

Given that subqueries can be ordered and the size of the result set can be limited, the following (untested) query might do what you want:

select COMPANY, (select avg(REPUTATION) 
                 from (select REPUTATION 
                       from COMPANY_BY_NEWS_REPUTATION
                       where COMPANY = TR.COMPANY
                       order by DATE desc
                       fetch first 5 rows only))
from (select distinct COMPANY 
      from COMPANY_BY_NEWS_REPUTATION) as TR

This query retrieves all distinct company names from COMPANY_BY_NEWS_REPUTATION, then retrieves the average of the last five reputation rows for each company. I have no idea whether it will perform sufficiently, that will likely depend on the size of your data set and what indexes you have in place.

If you have a list of unique company names in another table, you can use that instead of the select distinct ... subquery to retrieve the companies for which to calculate averages.