Partition by, dense rank

Posted on December 23, 2021Comments Off on 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)

patientvisit_idphysician
a1id_1
a2id_2
a3id_1
a4id_3
b5id_1
b6id_2
c7id_1
c8id_2
c9id_3

so the result would be:

patientvisit_id
a4
c9

Any suggestions?