MrK MrK - 4 months ago 12
SQL Question

MYSQL - Returned results GROUP BY and getting the max value from a joined table

Hard one to title... pretty much at the end of my rope with this hopefully someone will help me. There are similar questions posted but few seem to deal with joined tables / my limited skills in this area.

I have 2 tables - clients and interviews.

--clients--

id / first_name / surname / practitioner_id / status

--interviews--

id / client_id / planned_review_date

My aim is to do the seemingly simple task of returning a list of upcoming interview dates for the currently logged in practitioner.

I can return all the dates with the below code -

SELECT c.id,
c.first_name,
c.surname,
i.planned_review_date,
i.id AS IntID
FROM clients AS c
INNER JOIN interviews AS i
ON c.id=i.client_id
WHERE c.practitioner_id = 18
AND c.status <> 'Archived'
ORDER BY i.id DESC


This gives me something like -

id first_name surname planned_review_date IntID
20 Terry Stamp 2016-04-01 00:00:00 62
20 Terry Stamp 2016-03-01 00:00:00 61
19 Iam More 2016-07-01 15:47:57 60
19 Iam More 2016-08-20 00:00:00 59
10 Steve Powers 2016-04-20 00:00:00 58
10 Steve Powers 2015-03-20 00:00:00 57


I need only one date to show per client for their next interview I grouped them with

GROUP BY c.id


Which leads to a cut down list as I expect, one row per client. However the actual returned date is not the newest date (ie the one associates with the highest i.id for that client). I understand this is due to a limitation with the way GROUP BY works but I cannot for the life of me get subquerys to work in order to sort it beforehand.

id first_name surname planned_review_date IntID
20 Terry Stamp 2016-03-01 00:00:00 61
19 Iam More 2016-08-20 00:00:00 59
10 Steve Powers 2016-04-20 00:00:00 58


I require the above details but with the IntID's of 62,60 and 58. ie what i need returned is -

id first_name surname planned_review_date IntID
20 Terry Stamp 2016-04-01 00:00:00 62
19 Iam More 2016-07-01 15:47:57 60
10 Steve Powers 2016-04-20 00:00:00 58


As a note - the newest date under i.planned_review_date wont always be the "newest", we can assume the date tied to the highest i.id for that client will be the relevant newest date as it is the input review date on the most recent interview.

I know about max() but have not been able to implement it in a way that it returns the max(i.id) for the associated client as opposed to giving me the flat max - 62 in this case. I suspect this is the crux of what the subquery should handle.

Ive been at this a good while, creating what turned out to be more complex code versions of the above. Not my area having inherited this project but having to make the best of it. Im sure ive been close in my many, many tries but am having to put up the white flag - can someone help?

edit - updated as i had some of the returned fields in wrong order

Answer

Derive a subset which just contains the max date for each client ID and then join that back to your query as such: Assuming planned_Review_date is the "newest" data you're looking for.

The subquery returns the Client_ID and max planned date. By inner joining this back to your base set we limit that set to ONLY be for records with that max date.

SELECT c.id, 
       c.first_name, 
       c.surname,
       i.planned_review_date,
       i.id AS IntID
FROM clients AS c
INNER JOIN interviews AS i
  ON c.id=i.client_id
INNER JOIN (SELECT Client_ID, max(Planned_review_Date) MD 
            FROM interviews    
            GROUP BY client_ID) I2
     on I2.Client_ID = I.Client_ID 
    and I2.MD = I.Planned_Review_Date
WHERE c.practitioner_id = 18
  AND c.status <> 'Archived' 
ORDER BY i.id DESC
Comments