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
=3===2==2009-02-01=true
=4===2==2009-02-03=true
=5===2==2009-02-05=false
Then I needed rows with id==1 and id==4.
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:
Code:
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/ ... 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.
Login
FAQ
Search