Help on a SQL Query

Question

In a previous Post, I was doing a very simple database with 3 tables "doctor", "patient" and "visit". I've tried to make it more realistic and included now a many to many relationship between "doctor" and "patient". "visit" is the table resulting in this n-m relation. I assume the following simple structure for my table :

doctor
- idDoctor
- name

patient
-idPatient
-name
-dob

visit
-idVisit
-idPatient
-idDoctor
-timestamp

I'm using the following data on which I want to make a query :

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 have then 3 patients and 3 doctors. For example, the patient 1 went two times 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 the last visit. This query should return the id Visits (2,3,4,5,6, 7) and not 1 because the last visit that the patient paid to the doctor 1 was at 20:10 and not 14:10. How could I do that ?

I really appreciate you comments and your help. It really helps me to improve my designs as I begin using SQL.

Answer 1

Assuming ids are allocated in chronological sequence you can "cheat"

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

If that assumption can't 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 answer:

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