HQL query for entity with max value

java hibernate hql
rjsang picture rjsang · Dec 21, 2010 · Viewed 80.8k times · Source

I have a Hibernate entity that looks like this (accessors ommitted for brevity):

@Entity
@Table(name="FeatureList_Version")
@SecondaryTable(name="FeatureList",
    pkJoinColumns=@PrimaryKeyJoinColumn(name="FeatureList_Key"))
public class FeatureList implements Serializable {

    @Id
    @Column(name="FeatureList_Version_Key")
    private String key;

    @Column(name="Name",table="FeatureList")
    private String name;

    @Column(name="VERSION")
    private Integer version;

}

I want to craft an HQL query that retrieves the most up to date version of a FeatureList. The following query sort of works:

Select f.name, max(f.version) from FeatureList f group by f.name

The trouble is that won't populate the key field, which I need to contain the key of the record with the highest version number for the given FeatureList. If I add f.key in the select it won't work because it's not in the group by or an aggregate and if I put it in the group by the whole thing stops working and it just gives me every version as a separate entity.

So, can anybody help?

Answer

axtavt picture axtavt · Dec 21, 2010

The straightforward version of this query looks like this (assuming that (name, version) pairs are unique):

select f from FeatureList f 
where f.version = 
     (select max(ff.version) from FeatureList ff where ff.name = f.name)

Related questions

How do you do a limit query in JPQL or HQL?

In Hibernate 3, is there a way to do the equivalent of the following MySQL limit in HQL? select * from a_table order by a_table_column desc limit 0, 20; I don't want to use setMaxResults if possible. This definitely was possible …

Read More
What is the difference between JOIN and JOIN FETCH when using JPA and Hibernate

Please help me understand where to use a regular JOIN and where a JOIN FETCH. For example, if we have these two queries FROM Employee emp JOIN emp.department dep and FROM Employee emp JOIN FETCH emp.department dep Is …

Read More
ORDER BY using Criteria API

When I write a HQL query Query q = session.createQuery("SELECT cat from Cat as cat ORDER BY cat.mother.kind.value"); return q.list(); Everything is fine. However, when I write a Criteria Criteria c = session.createCriteria(Cat.class); …

Read More