Query to get the max of the max records

Question

Hello,

I Have a table with the following columns: patient_id, visit_id, and visit_date. How can I write a query to get the max(visit_id) on the most recent visit date for each patient? (several visit_id could occur on the same date for the same patient)

basically, I want to end up with NO duplicate patient ID's.

Thanks.

Answer 1

select patient_id, max(t.visit_id) from 
table t inner join ( 
    select patiend_id, max(visit_date) as latest_visit 
    from table 
    group by patient_id
) lv on 
t.patient_id = lv.patientID and t.visit_date = lv.latest_visit
group by t.patient_id

Answer 2

Just use GROUP BY and a subselect.

SELECT patient_id, visit_date, max(visit_id)
FROM tbl
JOIN (
  SELECT patient_id, max(visit_date) FROM tbl GROUP by patient_id
) AS t
  USING ( patient_id, visit_date )
GROUP BY patient_id, visit_date

Answer 3

select t.patient_id, max(t.visit_id)  
  from (select t.patient_id, max(t.visit_date) maxdate
          from table t
         group by t.patient_id) tt, table t
where t.patient_id = tt.patient_id
  and t.visit_date = tt.maxdate
group by t.patient_id 

Answer 4

Since you need to do 'max' on two columns, what you need is subqueries. I haven't tested the following query, but it should be something like:

SELECT patient_id, MAX(visit_id) AS maxvisit, maxdate
FROM (
    SELECT patient_id, visit_id, MAX(visit_date) AS maxdate
    FROM mytable
    GROUP BY patient_id, visit_id
) t
GROUP BY patient_id, maxdate

Hope this helps.