Hibernate criteria how to get rows with max column value

Question

I have a table like below:

       user_id   number
        1         10
        2         17
        1         12
        2         18

I mean one user can have more than one row. I have to reach following result:

       user_id   number
        1         12
        2         18

How can I achive this with hibernate criteria?

NewUser · Accepted Answer · 2021-06-25T06:08:40.897

4

You can try this.

Criteria cr = session.createCriteria(User.class);
cr.setProjection(
         Projections.projectionList()
            .add(Projections.groupProperty("userId"))
            .add(Projections.max("number"))
);

This is an untested code.

edited Jun 25 '21 at 06:08

answered Oct 22 '14 at 09:20

NewUser

3,729
10
57
79

score 2 · Answer 2 · answered Oct 22 '14 at 09:11

2

select u.id, max(u.number) from User u group by u.id

This is the HQL query you need. I don't see any point of translating this to criteria, but if you really want to do it, it shouldn't be hard. You'll need to use projections from the Projections class.

answered Oct 22 '14 at 09:11

JB Nizet

678,734
91
1,224
1,255

Thanks but I really need criteria way of this. I could not translate it to criteria either. – Gary Leather Oct 22 '14 at 09:15
1

Show a bit of effort. What have you tried? Where are you stuck? – JB Nizet Oct 22 '14 at 10:16

Hibernate criteria how to get rows with max column value

2 Answers2