Take the 2-minute tour ×

Using Hibernate's Criteria API, I want to select the record within a table with the maximum value for a given column.

I tried to use Projections, creating an alias for max(colunName), then using it in restrictions.eq(), but it keeps telling me "invalid number".

What's the correct way to do that with Hibernate?

share|improve this question

5 Answers 5

up vote 17 down vote accepted

You can use a DetachedCriteria to express a subquery, something like this:

DetachedCriteria maxId = DetachedCriteria.forClass(Foo.class)
    .setProjection( Projections.max("id") );
    .add( Property.forName("id").eq(maxId) )


share|improve this answer
i used it, but the something am not happy with is that: it need to run two queries, thanks again man –  Amr Faisal Oct 11 '10 at 10:38
@AmrFaisal See my answer below on how to do this with one single query. –  Hugo Jul 5 '12 at 18:56

I found that using addOrder and setMaxResults together worked for me.

Criteria c = session.createCriteria(Thingy.class);
return (Thingy)c.uniqueResult();

Using the MySQL dialect, this generates a SQL prepared statement about like this (snipping out some of the fields):

select this_.id ... from Thingy this_ order by this_.id desc limit ?

I am not sure if this solution would be effective for dialects other than MySQL.

share|improve this answer



and fetch just the first result :)

share|improve this answer
this is bad because you bring the entire list form the database then you discard every other records. Would not be very good in the case you have a huge table! –  renatoargh Apr 19 '12 at 20:45
No, I said fetch the first, this would be something like "SELECT ... LIMIT 1" or something (sorry, haven't used hibernate in a long while now). What you're saying is fetching several, and just using the first; they're different things. –  Hugo Apr 20 '12 at 3:13


from Person where person.id = (select max(id) from Person)

Untested. Your database needs to understand subselects in the where clause.

Too lazy to find out if/how such a subselect can be expressed with the criteria api. Of course, you could do two queries: First fetch the max id, then the entity with that id.

share|improve this answer
You are great man, i did use the second option, using two queries, but i was searching if it could be done using criteria api :) –  Amr Faisal Oct 10 '10 at 13:17

For the max() function in hibernate:

share|improve this answer
I think this only returns the encounterId instead of the record object. –  jamesdeath123 Dec 9 at 5:12

Your Answer


