[jboss-user] [Persistence, JBoss/CMP, Hibernate, Database] - Help composing HQL query

[sanches]

Hello all, 

I am trying to compose a statement which will fetch the latest "active" row from the table.

If the table would contain:

=id=obj_id=timestamp=active
--------------------------------
=0===1==2009-01-01=true
=1===1==2009-01-03=true
=2===1==2009-01-05=false

Then I needed row with id==1.

Obviously, it's better to run this in single query.
I guess this is inevitable to employ GROUP BY clause, but I was not able to compose query which Hibernate would accept (I use HSQL as DB)

I must use MAX() aggregate function since I need the latest timestamp.
I must group by obj_id since there are many records for object with the same id.

Therefore, the following query would satisfy:
select o2 from Orders o2 inner join 
  | (select max(o1.timestamp), o1.obj_id from Orders o1 
  | WHERE o1.active=true GROUP BY o1.obj_id) as o3 
  | on o2.obj_id=o3.obj_id

But such query containing subquery isn't accepted.
All examples of subqueries on http://docs.jboss.org/hibernate/stable/core/reference/en/html/queryhql.html#queryhql-subqueries
- either generates one-column-tables and use IN 
- or use subqueries which return single row and use equality test.

Subquery above may return more than one row, can not be single-column as I must use both MAX() and GROUP BY id.

I think join is only solution here, but can not find formula.

Thank you.

View the original post : http://www.jboss.org/index.html?module=bb&op=viewtopic&p=4247563#4247563

Reply to the post : http://www.jboss.org/index.html?module=bb&op=posting&mode=reply&p=4247563