Oracle 19: Find ID of a filtered subset of rows based on the maximum value of a third column

Given a "summary" table with 4 or more columns:

(ID1, ID2, SortOrder, BooleanYN1, BooleanYN2)

For each value of ID1, I want the value of ID2 that corresponds to the row with BooleanYN{n} = Y that has the highest value of SortOrder, if any such records exist.

Example:

I’m looking for (1, 30, NULL), since the "most recent" (based on SortOrder) row for ID1 = 1 that has BooleanYN1 = 'Y' is the row with ID2 = 30, and there are no rows where BooleanYN2 = 'Y'.

I built a SQL Fiddle with example data here:

http://sqlfiddle.com/#!4/a5bc5/6

This query doesn’t work in the Fiddle because Oracle 11g doesn’t support OUTER APPLY, but it works in Oracle 19.

SELECT
  p.person_id,
  mmr.appointment_id,
  flu.appointment_id,
  covid.appointment_id,
  hiv.appointment_id
FROM
  person p
  OUTER APPLY (
    SELECT vs.appointment_id
      FROM vaccination_summary vs
     WHERE vs.person_id = p.person_id
       AND mmr_yn = 'Y'
     ORDER BY vs.appointment_date DESC
     FETCH FIRST ROW ONLY
  ) mmr
  OUTER APPLY (
    SELECT vs.appointment_id
      FROM vaccination_summary vs
     WHERE vs.person_id = p.person_id
       AND flu_yn = 'Y'
     ORDER BY vs.appointment_date DESC
     FETCH FIRST ROW ONLY
  ) flu
  OUTER APPLY (
    SELECT vs.appointment_id
      FROM vaccination_summary vs
     WHERE vs.person_id = p.person_id
       AND covid_yn = 'Y'
     ORDER BY vs.appointment_date DESC
     FETCH FIRST ROW ONLY
  ) covid
  OUTER APPLY (
    SELECT vs.appointment_id
      FROM vaccination_summary vs
     WHERE vs.person_id = p.person_id
       AND hiv_yn = 'Y'
     ORDER BY vs.appointment_date DESC
     FETCH FIRST ROW ONLY
  ) hiv;

It seems SUPER inelegant and inefficient.

Surely there must be a better way to do this with window functions…or maybe un-pivoting and then re-pivoting?

>Solution :

This sounds like a good candidate for the keep dense_rank aggregate function, e.g. for your example in the sqlfiddle:

SELECT p.person_id,
       p.person_name,
       max(case when mmr_yn = 'Y' then appointment_id end) keep (dense_rank first order by case when mmr_yn = 'Y' then appointment_date end desc nulls last) latest_mmr_vaccine_appt_id,
       max(case when flu_yn = 'Y' then appointment_id end) keep (dense_rank first order by case when flu_yn = 'Y' then appointment_date end desc nulls last) latest_flu_vaccine_appt_id,
       max(case when covid_yn = 'Y' then appointment_id end) keep (dense_rank first order by case when covid_yn = 'Y' then appointment_date end desc nulls last) latest_covid_vaccine_appt_id,
       max(case when hiv_yn = 'Y' then appointment_id end) keep (dense_rank first order by case when hiv_yn = 'Y' then appointment_date end desc nulls last) latest_hiv_vaccine_appt_id
  FROM person p
  JOIN vaccination_summary vs
    ON vs.person_id = p.person_id
 group by p.person_id, p.person_name
 ORDER BY p.person_id;

See the sqlfiddle for the results.