How to select rows with latest date from 2 tables

[cartergarth]

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.

Thank you!

[pritaeas]

Try something like this:

SELECT
  users.user_id,
  users.firstname,
  users.middlename,
  users.lastname,
  user_shift_schedule.shift_id,
  user_shift_schedule.effectivity_date
FROM 
  users u,
  user_shift_schedule uss
WHERE u.user_id = uss.user_id
GROUP BY uss.user_id
ORDER BY 
  uss.user_id, 
  uss.effectivity_date DESC

[cartergarth]

Thanks. But I already resolved this in that day. Your solution did not work. Here is what I've got:

SELECT u.user_id,u.firstname,u.middlename,u.lastname,s.shift_id,s.effectivity_date FROM users u,user_shift_schedule s,(SELECT user_id,max(effectivity_date) maxdate FROM user_shift_schedule GROUP BY user_id) m WHERE u.user_id=s.user_id AND u.user_id=m.user_id AND s.effectivity_date=m.maxdate