Partition by, dense rank


I have the following table describing patients visits: Each patient has a visit_id for which he sees a given physician. I am trying to extract the value of the visit_id for which saw his 3rd physician. (3rd physician and not 3rd visit)

patient visit_id physician
a 1 id_1
a 2 id_2
a 3 id_1
a 4 id_3
b 5 id_1
b 6 id_2
c 7 id_1
c 8 id_2
c 9 id_3

so the result would be:

patient visit_id
a 4
c 9

Any suggestions?


The following statement returns your result. The innermost subquery eliminates multiple visits to the same physician, then row_number() counts the visits and the outermost select gets the third physician.

select patient, visit 
  from (select patient, visit, row_number() over (partition by patient order by visit) rn
          from  (  select patient, min(visit) as visit 
                     from tab 
                 group by patient, physician
                ) t1
       ) t2
 where t2.rn = 3


patient visit_id
a 4
c 9

See db<>fiddle

Answered By – buddemat

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply

(*) Required, Your email will not be published