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:
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
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.