JPA/Hibernate query construction

Question

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

1. client_id, foreign key mapping to client table.
2. 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

Answer 1

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.

Answer 2

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