0 Replies Latest reply on Aug 3, 2009 7:13 AM by Alexander Lengson

    Help composing HQL query

    Alexander Lengson Newbie

      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.