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?

score 62 · Accepted Answer · answered Dec 21 '10 at 12:52

62

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)

answered Dec 21 '10 at 12:52

axtavt

228,184
37
489
472

1

Indeed, its simple and more efficient. Actually, looking at `group by` in the original query made me think around that all that while. I would consider this, one step further in restructuring my proposed query. +1 – Adeel Ansari Dec 21 '10 at 15:12

score 13 · Answer 2 · edited May 23 '17 at 12:34

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

Very nice. +1 for the criteria implementation – Jose Sep 25 '15 at 05:21 — Jose, Sep 25 '15 at 05:21

score 10 · Answer 3 · answered Jan 30 '13 at 13:41

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.

In order to use limit with hql, use `query.setMaxResults(1);`. Credits at http://stackoverflow.com/a/1239745/1064325 — falsarella, Oct 27 '13 at 18:23

Adeel Ansari · Answer 4 · 2010-12-21T11:37:05.677

3

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.

edited Dec 21 '10 at 11:37

answered Dec 21 '10 at 09:27

Adeel Ansari

38,068
12
89
127

No, that doesn't work because you can't put compare two values to the subquery returned. Hibernate returns a SQLGrammarException complaining about the , after fl.name – rjsang Dec 21 '10 at 09:43
@Rob: Updated my answer for further discussion. – Adeel Ansari Dec 21 '10 at 09:50
Unfortunately, it's not a valid query in MSSQL, which it needs to run against. Name and version together is unique, but if you add key into the grouping you get every combination of key, name and version which means every different version instead of just the latest one (with the highest version number) – rjsang Dec 21 '10 at 09:53
@Rob: No, you get combination of `key` and `name`, `version` is not the part of `group by` here. If the pair is unique, then my proposed 2nd query will return the same and same number of records. Try that out and let me know. – Adeel Ansari Dec 21 '10 at 09:58
Oh right. No, the key and name pair is not unique. The key is the key to the unique pairing of name and version, so a single name has as many keys as it has versions. – rjsang Dec 21 '10 at 10:10
@Rob: Yes, by unique I mean `name` and `version`. If there is a unique `key` for the pair, `name` and `version`, then my query will yield the same result. Try that out. And let me know if there is some difference. – Adeel Ansari Dec 21 '10 at 10:56

score 0 · Answer 5 · answered Dec 21 '10 at 16:55

0

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

answered Dec 21 '10 at 16:55

Matt Brock

5,123
1
24
26

HQL query for entity with max value

5 Answers5