How to select rows with latest date from 2 tables
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!
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!
-
KirkMcD -
Thanks
{{ DiscussionBoard.errors[5577565].message }} -