Menu
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!