SQL query help

Question

In a previous post, I made a very simple database with three tables "doctor", "patient" and "visit". I tried to make it more realistic and included many, many relationships between the "doctor" and the "patient." "visit" is the table leading to this nm relation. I assume the following simple structure for my table:

  doctor
 - idDoctor
 - name

 patient
 -idPatient
 -name
 -dob

 visit
 -idVisit
 -idPatient
 -idDoctor
 -timestamp

I use the following data for which I want to make a request:

  idVisit idDoctor idPatient timestamp
 1 1 1 2010-07-19 14:10
 2 1 2 2010-07-19 15:10
 3 2 1 2010-07-19 15:10
 4 3 1 2010-07-19 16:10
 5 2 2 2010-07-19 18:10
 6 2 3 2010-07-19 19:10
 7 1 1 2010-07-19 20:10

I then have 3 patients and 3 doctors. For example, patient 1 went twice to see doctor 1, one-time doctor 2, and one-time doctor 3.

I would like to build my request so that for each couple (doctor, patient) I have a last visit. This request should return id Visits (2,3,4,5,6, 7), not 1, because the last visit the patient paid to doctor 1 was at 20:10, not 14:10. How can i do this?

I really appreciate your comments and your help. It really helps me improve my projects when I start using SQL.

Answer 1

Assuming the identifiers are highlighted in chronological order, you can "trick"

SELECT MAX(idVisit) AS idVisit, idDoctor, idPatient FROM visit group by idDoctor, idPatient 

If this assumption cannot be made

 SELECT v.idVisit, v.idDoctor, v.idPatient FROM visit v WHERE NOT EXISTS ( SELECT * FROM visit v2 WHERE v2.idDoctor = v.idDoctor AND v.idPatient = v2.idPatient AND v2.timestamp > V.timestamp ) 

Or, if your DBMS supports this,

 WITH V AS ( SELECT idVisit, idDoctor, idPatient, row_number() over(partition by idDoctor, idPatient order by timestamp desc) AS RN FROM visit ) SELECT idVisit, idDoctor, idPatient FROM V WHERE RN=1 

Answer 2

 select v.idVisit, d.idDoctor, d.name as nameDoctor, p.idPatient, p.name as namePatient, v.timestamp as lastVisit from visit v join doctor d on v.idDoctor = d.idDoctor join patient p on v.idPatient = p.idPatient where not exists (select 1 from visit where idDoctor = v.idDoctor and idPatient = v.idPatient and timestamp > v.timestamp) 

Answer 3

Oracle analytics response:

 SELECT idVisit, idDoctor, idPatient, max(timestampVisit) over (partition by idDoctor, idPatient) as timestampVisit FROM visits