HQL query for entity with max value

Question

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 1

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) 

Answer 2

I made a scenario here,

---

Table

key          name                 version          ----------- -------------------- -----------  1           adeel                1            2           adeel                2            3           adeel                3            4           ahmad                1            5           ahmad                2            6           ahmad                3            7           ahmad                4            8           ansari               1            9           ansari               2            10          ansari               3            

---

Result using your original query

>> select f.name, max(f.version) from FeatureList f group by f.name  name                 max(f.version)  -------------------- ------------  adeel                3             ahmad                4             ansari               3             

---

Result using your desired query

>> select fl.* from FeatureList fl     where (fl.name, fl.version) in (select f.name, max(f.version)                                             from FeatureList f group by f.name);  key          name                 max(fl.version)   ----------- -------------------- -----------  3           adeel                3            7           ahmad                4            10          ansari               3            

NB: Tried it using MySQL this time. Its working. I am pretty sure MS SQL Server also have IN (...) operator. You just need to use session.createNativeQuery() in Hibernate.

---

Edited to work on axtavt's answer

As we found out this can be made as simple as,

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

Now try the same using Hibernate Criteria API,

DetachedCriteria versions = DetachedCriteria.forClass(FeatureList.class, "f")     .setProjection( Property.forName("f.version").max())     .add(Property.forName("f.name").eqProperty("fl.name"));  session.createCriteria(FeatureList.class, "fl")     .add( Property.forName("fl.version").eq(versions) )     .list();