How to select rows with latest date from 2 tables

Dev Shed Forums Sponsor:

February 7th, 2012, 11:17 PM

cartergarth

Contributing User

Join Date: Feb 2011

Posts: 90

Time spent in forums: 18 h 33 m 8 sec
Reputation Power: 2

Code Critique - 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!

__________________
Providing inbound and outbound support

Call Center Philippines | Customer Service Outsourcing

BPO Services Philippines | Call Center Outsourcing Services Philippines

February 8th, 2012, 09:29 AM

SecurityDavid

Contributing User

Join Date: Aug 2011

Posts: 135

Time spent in forums: 3 Days 6 h 14 m
Reputation Power: 52

You don't have any type of WHERE statement, so you are getting all grouped results. I would suggest that you don't want to select MAX(user_shift_schedule.effectivity_date), what you want is to select effectivity_date WHERE effectivity_date=MAX(effectivity_date)

Code:

SELECT
users.user_id,
users.firstname,
users.middlename,
users.lastname,
user_shift_schedule.shift_id,
user_shift_schedule.effectivity_date

FROM users
JOIN user_shift_schedule

ON users.user_id=user_shift_schedule.user_id

WHERE user_shift_schedule.effectivity_date=(
          SELECT MAX(effectivity_date) FROM user_shift_schedule
          )

GROUP BY user_shift_schedule.user_id

February 8th, 2012, 09:44 AM

cafelatte

Contributing User

Dev Shed Intermediate (1500 - 1999 posts)

Join Date: Mar 2008

Posts: 1,621

Time spent in forums: 1 Month 19 h 23 m 23 sec
Reputation Power: 374

An uncorrelated version...

Code:

SELECT u.*
     , x.shift_code
     , x.effectivity_date
  FROM user_shift_schedule x
  JOIN
     ( SELECT user_id
            , MAX(effectivity_date) max_effectivity_date
         FROM user_shift_schedule
        GROUP
           BY user_id
     ) y
    ON y.user_id = x.user_id
   AND y.max_effectivity_date = x.effectivity_date
 RIGHT
  JOIN users u 
    ON u.user_id = x.user_id;

February 10th, 2012, 03:51 AM

cartergarth

Contributing User

Join Date: Feb 2011

Posts: 90

Time spent in forums: 18 h 33 m 8 sec
Reputation Power: 2

Thanks anyway. I already solved this in that day. Here is what I've got:

Quote:

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