JPA/Hibernate query construction

Question

I am fetching records from my "record" table. "record" table has many columns tow of which are

client_id, foreign key mapping to client table.
creation_date , date of record creation

I would like to do a query on this table , but I would like to fetch only one record per client(latest creation_date record has preference).

Will following work?

select r.id,r.xx,r.yy
group by(r.client_id),r.creation_date 
from record r 
order by creation_date desc

I tried above and seems records fetched are not of latest creation dates. Hope my question is clear

score 1 · Answer 1 · edited May 23 '17 at 10:34

1

Just keep your query and add a WHERE condition :

SELECT r.id,r.xx,r.yy
GROUP BY(r.client_id)
FROM record r
WHERE r.creation_date = (SELECT MAX(creation_date) FROM record tmp WHERE tmp.client_id = r.client_id )

Take a look at This discussion

edited May 23 '17 at 10:34

Community

1
1

answered May 03 '12 at 14:02

M3HD1

1,424
5
34
50

maple_shaft · Accepted Answer · 2012-05-03T14:24:40.490

0

This should give you a good starting point in HQL.

from Record as r inner join fetch r.client
where r.creation_date > (
    select max(rec.creation_date) from Record rec
    where rec.client.client_id = r.client.client_id
)

This of course assumes that your Record has a reference to its parent Client called client.

edited May 03 '12 at 14:24

answered May 03 '12 at 13:55

maple_shaft

10,389
6
44
70

1

Doesn't `max(rec.creation_date)` get the max for the entire DB; not for the group? – beerbajay May 03 '12 at 14:21
@beerbajay Good catch... I fixed it so that for each record of `r` it will get the max creation date for `r` client_id. – maple_shaft May 03 '12 at 14:25

JPA/Hibernate query construction

2 Answers2