[jboss-user] [Persistence, JBoss/CMP, Hibernate, Database] - Help composing HQL query
do-not-reply at jboss.com
Mon Aug 3 07:13:13 EDT 2009
I am trying to compose a statement which will fetch the latest "active" row from the table.
If the table would contain:
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.
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
More information about the jboss-user