HQL query for entity with max value

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(na开发者_StackOverflow中文版me="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?

---

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)

---

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();

---

Old question but I thought I'd provide my experience here as well for future users:

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

This works great, but if the following holds:

• MySql v5.5 w/ InnoDB engine
• You know exactly how many result rows you want (OC implies that s/he wants exactly 1 row, but the above query would work for a varying number of results)

the following appears to be very significantly faster:

FROM FeatureList ORDER BY version DESC LIMIT 1;

My trials on a similar query with 700,000 records in the table takes around 0.19s for the former version and 0.05s for the latter.

---

How about this,

from FeatureList fl where (fl.name, fl.version) in (
               select f.name, max(f.version) from FeatureList f group by f.name)

Note: Try with the createNativeQuery(), its a valid Oracle query, not sure about any other database.

---

One question, is the pair, name and version, unique? If yes, then its fine, otherwise if the pair is not unique, what do you think how a key will be selected? Suppose one of the record from the original query is,

f.name         max(f.version)
------         --------------
Django         1.2

and assume there are 3 keys having the same pair. Now answer, which key should be assigned to this record? I hope you are getting my point.

---

How about using distinct + order by instead of group by?

select f.id, distinct(f.name), f.version from FeatureList f order by f.version desc