title: HQL and Hibernate Criteria with Having and GROUP BY clause
body: <p>I have Problem when I executing the following Hibernate HQL Query:</p>
  
  <pre><code>String hql = "from ProcessInstanceHistory where processInstance.processInstanceId=122 group by newStatusId having max(processModifiedDate)";
  Query query = session.createQuery(hql);
  
  List qResult = query.list();
  ProcessInstanceHistory processInstanceHistory = (ProcessInstanceHistory) qResult.get(0);
  </code></pre>
  
  <p>It gives me Following error when I run above script without <code>HAVING</code> clause:</p>
  
  <pre><code>org.hibernate.exception.SQLGrammarException: could not execute query
      at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
      at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
      at org.hibernate.loader.Loader.doList(Loader.java:2223)
      at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)
      at org.hibernate.loader.Loader.list(Loader.java:2099)
      at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:378)
      at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:338)
      at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:172)
      at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1121)
      at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)
      at Test.main(Test.java:140)
  Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 'tne_travel_7_4_1.processins0_.PI_HISTORY_ID' isn't in GROUP BY
      at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
      at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
      at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
      at java.lang.reflect.Constructor.newInstance(Unknown Source)
      at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
      at com.mysql.jdbc.Util.getInstance(Util.java:381)
      at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1030)
      at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
      at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3515)
      at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3447)
      at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1951)
      at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2101)
      at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2554)
      at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1761)
      at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1912)
      at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)
      at org.hibernate.loader.Loader.getResultSet(Loader.java:1787)
      at org.hibernate.loader.Loader.doQuery(Loader.java:674)
      at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
      at org.hibernate.loader.Loader.doList(Loader.java:2220)
      ... 8 more
  </code></pre>
  
  <p>And when I use <code>HAVING</code> clause it gives:</p>
  
  <pre><code>org.hibernate.hql.ast.QuerySyntaxException: unexpected AST node: max near line 1, column 117 [from tne.nexstep.dao.ProcessInstanceHistory where processInstance.processInstanceId=122 group by newStatusId having max(processModifiedDate)]
      at org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:31)
      at org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:24)
      at org.hibernate.hql.ast.ErrorCounter.throwQueryException(ErrorCounter.java:59)
      at org.hibernate.hql.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:235)
      at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:160)
      at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:111)
      at org.hibernate.engine.query.HQLQueryPlan.&lt;init&gt;(HQLQueryPlan.java:77)
      at org.hibernate.engine.query.HQLQueryPlan.&lt;init&gt;(HQLQueryPlan.java:56)
      at org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:72)
      at org.hibernate.impl.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:133)
      at org.hibernate.impl.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:112)
      at org.hibernate.impl.SessionImpl.createQuery(SessionImpl.java:1623)
      at Test.main(Test.java:138)
  </code></pre>
  
  <p>I had also try with Hibernate Criteria but it won't work.</p>
  
  <p>Is their any other way to acheive the same task ?</p>
  
  <p>Thanks in Advance </p>