CODEWITHSUNDEEP
javahibernateorm
Ganesamoorthy
Ganesamoorthy

Reputation: 3675

get max value record from table in hibernate

How to get max value record from table in hibernate?

Upvotes: 33

Views: 85827

Answers (4)

kang sanda
kang sanda

Reputation: 61

You could use sub-query:

SELECT * FROM Employee WHERE age IN (SELECT MAX(age) age FROM Employee)

Upvotes: 6

Ravi Mundoli
Ravi Mundoli

Reputation: 271

AFAIK, Projections will only retrieve a subset of the columns (or is it just one column?) you want.

If your data object is like so:

class Person {
    private String id;
    private String name;
    private int age;
    ...
}

and want the oldest person in the table, the following seems to work:

...
Person oldest = 
    (Person) session.createCriteria(Person.class)
    .addOrder(Order.desc("age"))
    .setMaxResults(1)
    .uniqueResult();
...

The Hibernate log (with show_sql, format_sql, and use_sql_comments all set to true) shows

select
    *
from
    ( /* criteria query */ select
        this_.ID as ID1_12_0_,
        this_.NAME as NAME_12_0_,
        this_.AGE as AGE_12_0_
    from
        PERSON this_
    order by
        this_.AGE desc )
where
    rownum <= ?

Which seems correct. Note that this is on Hibernate 3.3.2 with Oracle 11 XE.

Upvotes: 27

Pascal Thivent
Pascal Thivent

Reputation: 570315

Use the max(...) aggregate function:

select max(cat.weight) from Cat cat

Reference

Upvotes: 8

Darin Dimitrov
Darin Dimitrov

Reputation: 1038720

You could use a projection:

Criteria criteria = session
    .createCriteria(Person.class)
    .setProjection(Projections.max("age"));
Integer maxAge = (Integer)criteria.uniqueResult();

Upvotes: 61

Related Questions