How to pick the latest record with GROUP BY userId

Question

In my application I want to find the latest duty of each user from 'StaffDuty' table using hibernate query (i.e. HQL). Below is my query.

query = session.createQuery("FROM StaffDuty where deptId.id = :dId and DATE(startDate) <= DATE(:selDate) and DATE(endDate) >= DATE(:selDate) group by staffId.id order by createdOn desc");

But this query doesn't return the latest record of each staff because the 'GROUP BY' clause execute before the 'ORDER BY' clause. How can I retrieve the latest records of all staff using HQL? I am using MySQL in backend.

id	staffId	deptId	startDate	endDate	duty	createdOn
1	s45	d5	2025-07-17	2025-07-31	Cleaning	2025-06-08 07:21:51
2	s35	d4	2025-06-14	2025-08-17	Pantry	2025-06-10 09:25:25
3	s18	d5	2025-06-27	2025-07-30	Office	2025-06-10 09:43:33
4	s35	d4	2025-06-25	2025-08-30	Cleaning	2025-06-11 09:21:51
5	s45	d5	2025-07-07	2025-08-11	Pantry	2025-07-01 08:15:38
6	s35	d5	2025-07-10	2025-08-22	Cleaning	2025-07-02 10:43:09
7	s18	d5	2025-07-17	2025-07-31	Accounts	2025-07-02 11:21:51
8	s35	d5	2025-07-11	2025-08-29	Office	2025-07-03 09:11:12
9	s45	d4	2025-07-18	2025-07-30	Cleaning	2025-07-05 07:21:51
10	s18	d4	2025-07-15	2025-08-24	Accounts	2025-07-05 09:33:35

The expected result when dId = d5 and selDate = 28-07-25 is:

id	staffId	deptId	startDate	endDate	duty	createdOn
5	s45	d5	2025-07-07	2025-08-11	Pantry	2025-07-01 08:15:38
7	s18	d5	2025-07-17	2025-07-31	Accounts	2025-07-02 11:21:51
8	s35	d5	2025-07-11	2025-08-29	Office	2025-07-03 09:11:12

But I got records with id 5,6,7 as result.

StaffDuty class is as below :

@Entity
@Table(name = "staff_duty")
public class StaffDuty implements Serializable {

 @Id
 @GeneratedValue(strategy = GenerationType.IDENTITY)
 @Column(name = "id")
 private long id;

 @JoinColumn(name = "staff_id", referencedColumnName = "id", nullable = false)
 @OneToOne(optional = false)
 private Staff staffId;

 @JoinColumn(name = "dept_id", referencedColumnName = "id", nullable = false)
 @OneToOne(optional = false)
 private Section deptId;

 @Column(name = "start_date")
 @JsonAdapter(DateJsonSerializer.class)
 private Date startDate;

 @Column(name = "end_date")
 @JsonAdapter(DateJsonSerializer.class)
 private Date endDate;

 @Column(name = "duty")
 private String duty;

 @Column(name="created_on")
 private Timestamp createdOn;

   ------------

}

You could switch to a NOT EXISTS subquery, fetch the record where there is no newer record with the same staff.id within the same intervall — Turo
– Turo, Commented Jul 29 at 9:10

Tim Biegeleisen · Accepted Answer · 2025-07-28 11:34:12Z

1

HQL is not very suitable for your use case, as it does not support the ROW_NUMBER() window function. You may use the following native query here:

SELECT *
FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY staffId ORDER BY createdOn DESC) rn
    FROM Staff_Duty
) t
WHERE rn = 1;

Your Java code might look something like the following:

List<Object[]> results = entityManager.createNativeQuery(
"SELECT * " +
"FROM ( " +
    "SELECT *, ROW_NUMBER() OVER (PARTITION BY staffId ORDER BY createdOn DESC) rn " +
    "FROM Staff_Duty " +
") t " +
"WHERE rn = 1"
).getResultList();

answered Jul 28 at 11:34

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Luca Basso Ricci Jul 28 at 11:45

Hibernate should supports row_number() out of the box from version 7 (IDK in prior version) with 'Window functions` (docs.jboss.org/hibernate/orm/7.0/querylanguage/html_single/…)

KJEjava48 Jul 28 at 12:09

@Luca Basso Ricci Can u make it as an answer for my query

Luca Basso Ricci Jul 28 at 14:12

Sorry, I haven't spare time to give an answer with a working example but I suppose native and HQL syntax doesn't differs too much

KJEjava48 Jul 28 at 12:39

@Tim Biegeleisen what exactly is rn and t here?? and why ROW_NUMBER() used here?

KJEjava48 Jul 28 at 12:41

@Tim Biegeleisen Can u make it as per my query condition and result as List<StaffDuty>

Tim Biegeleisen Jul 28 at 13:08

I can't do that without knowing the definition of the StaffDuty class.

KJEjava48 Jul 29 at 5:12

@TimBiegeleisen Ok I have updated the question with StaffDuty Class

Turo · Accepted Answer · 2025-07-29 12:30:28Z

0

I'm not sure how to write it in HQL with a NOT EXISTS query, something like:

FROM StaffDuty staff1
  where deptId.id = :dId 
    and DATE(startDate) <= DATE(:selDate) 
    and DATE(endDate) >= DATE(:selDate) 
    and not exists (FROM StaffDuty staff2
        where staff2.deptId.id = :dId 
          and staff1.staffId = staff2.staffId
          and DATE(staff2.startDate) <= DATE(:selDate) 
          and DATE(staff2.endDate) >= DATE(:selDate) 
          and staff2.createdOn > staff1.createdOn)

So to get the record around the seldate from each staff, where there is no record that also has suiting values with a later creation date

edited Jul 29 at 12:30

answered Jul 29 at 10:15

Turo

5,0442 gold badges17 silver badges30 bronze badges

Collectives™ on Stack Overflow

How to pick the latest record with GROUP BY userId

2 Answers 2

7 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related