Geek asks and answers

JavaDB: get ordered records in a subquery

I have the following "COMPANIES_BY_NEWS_REPUTATION" in my JavaDB database (this is some random data 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.12345        | 2011-05-19 15:43:28.0

One news_hash may relate to several companies, while a company may relate to several news. Reputation and date are tied to news_hash.

What I need to do is to calculate the average reputation of the last 5 news for each company. For this, I somehow feel that I need the user to “order” and “shift” in the 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 does not allow either ORDER BY or OFFSET in a subquery. Can someone suggest a working solution for my problem?

+3
1

JavaDB ? TableSubquery JavaDB, order by fetch next, , 10.6.2.1.

, , () , :

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

COMPANY_BY_NEWS_REPUTATION, . , , , , , .

, select distinct ... , .

+1

More articles:


All Articles