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?