Issue
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?
Solution
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
Result:
| 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