[FIXED] HIbernate Group by and Order By not working when used at the same time?

Issue

when i try to group and order by at the same time with hibernate, it just group by, order by seems to be ignored. Why is that so, how i can use them togehter?

list = session.createQuery("SELECT a FROM Student a GROUP BY a.firstname ORDER BY (a.date) DESC ", Student.class).getResultList(); 

Solution

JPA specification imposes the following restriction on the group by clause (see section 4.7 GROUP BY, HAVING):

... The requirements for the SELECT clause when GROUP BY is used follow those of SQL: namely, any item that appears in the SELECT clause (other than as an aggregate function or as an argument to an aggregate function) must also appear in the GROUP BY clause. In forming the groups, null values are treated as the same for grouping purposes.

Grouping by an entity is permitted. In this case, the entity must contain no serialized state fields or lob-valued state fields that are eagerly fetched. Grouping by an entity that contains serialized state fields or lob-valued state fields is not portable, since the implementation is permitted to eagerly fetch fields or properties that have been specified as LAZY.

Grouping by embeddables is not supported. ...

So, taken this in mind, the correct query may look like this:

List<Object[]> result = session.createQuery(
    "select a.firstname, max(a.date) as max_date " +
    "from Student a " +
    "group by a.firstname " +
    "order by max_date", Object[].class )
.getResultList();

Answered By - SternK
Answer Checked By - Gilberto Lyons (JavaFixing Admin)