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.
