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