问窗口函数

EN

 SELECT MIN(CASE WHEN A.VISIT = 'Y' THEN A.DATE END) OVER (PARTITION BY ID)  AS MIN_DT,
        FIRST_VALUE(CASE WHEN A.VISIT = 'Y' THEN A.ASSOCID END) KEEP (DENSE_RANK FIRST OVER (PARTITION BY ID ORDER BY A.VISIT DESC, A.DATE ASC),

SELECT MAX(CASE WHEN Date = MIN_DT THEN ASSOCID END) OVER (PARTITION BY ID)

select id, min(date),
       first_value(associd) over (partition by id order by date)
from t
where visit = 'Y'
group by id;

select t.*
from table t
where visit = 'Y' and
      date= (select min(t1.date) from table t1 where t1.id = t.id);