I have two tables, visits and encounters. Each Visit by a student may have several encounters, at different times. I would like a query with visitid, encounterid, and encounterdate showing ONLY the latest encounter for each visit, My results MUST include visits with no encounters.

My tables ;

What I have tried

select 
       Visits.visit_id, 
       Encounters.encounter_id,
       Encounters.encounter_datetime
FRom Visits
     LEFT OUTER JOIN Encounters
     ON Visits.visit_id = Encounters.visit_id
    INNER JOIN ( 
          select  Encounters.visit_id, MAX(Encounters.encounter_datetime)as Latest 
          from Encounters 
          group by Encounters.visit_id
     ) as NewEncounters 
    ON Encounters.visit_id = NewEncounters.visit_id 
       AND Encounters.encounter_datetime = NewEncounters.Latest

This returns the results I want, HOWEVER, Visits without encounters are not in the results.

CodePudding user response：

I actually don't see a clean way to salvage your direct join approach, but if your database support ROW_NUMBER, it is an easy option:

WITH cte AS (
    SELECT v.visit_id, e.encounter_id, e.encounter_datetime,
           ROW_NUMBER() OVER (PARTITION BY v.visit_id ORDER BY e.encounter_datetime DESC) rn
    FROM Visits v
    LEFT JOIN Encounters e ON v.visit_id = e.visit_id
)

SELECT visit_id, encounter_id, encounter_datetime,
FROM cte
WHERE rn = 1;

CodePudding user response：

For the problem of getting the max of several dates I give an (untested! Sorry) code example, which, however, points out the line of approach.

Select 
       Visits.visit_id, 
       a.encounter_id,
       max(a.encounter_datetime) as Max_Datetime
FRom Visits
     LEFT OUTER JOIN Encounters a
     ON Visits.visit_id = a.Encounters.visit_id
     inner join 
    Encounters b
    on a.visit_id=b.visit_id
    and
    a.encounter_datetime<=b.encounter_datetime
group by
       Visits.visit_id, 
       a.encounter_id,
       a.encounter_datetime;

For visits without encounters you can union a query with a where clause using Is Null. Maybe your database needs some syntactic fumbling with ; etc.