I have 2 tables:
1. users - user_id(PK), firstname, middlename, lastname
2. user_shift_schedule - shift_code(PK), user_id(FK), effectivity_date

I want to get all rows with the latest effectivity_date for each user_id.

This is what I've got so far:
[CODE]
SELECT
users.user_id
,users.firstname
,users.middlename
,users.lastname
,user_shift_schedule.shift_id
,MAX(user_shift_schedule.effectivity_date)

FROM users
JOIN user_shift_schedule

ON users.user_id=user_shift_schedule.user_id

GROUP BY user_shift_schedule.user_id
[CODE]

This would give me a somewhat desired result only the shift_id is incorrect, seems like it's selecting the most recently saved shift_id and not the shift_id that is of the same row as the latest effectivity_date of each user_id.


Any suggestion is greatly appreciated.

Thank you!