Extension of question many to many relationships in same table.

I got a single table and it contain 4 fields

Id|Hospital|   Doctor|patient

1     A        D1      P11

2     B        D6      P61

3     A        D2      P21

4     A        D1      P12

5     B        D7      P71

6     B        D6      P62

7     B        D6      P63

Doctors are unique to the Hospital. They don't work in other hospitals. Patients are unique to the doctor they don't visit any other doctor. Each hospital is having multiple Doctors.

If you observe there are multiple patients for each doctor.

Now the question is: How can I get "only one patient" related to the each doctor. It can be any patient from the record.

I am looking forward to see some thing like this

 Hospital Doctor Patient
  A       D1      P11

  A       D2      P21

  B       D6      P61

  B       D7      P71

I got the answer like select Hospital,doctor, max(patient) from table GROUP BY Hospital,Doctor ORDER BY Hospital,Doctor;

How to get the id also which is unique from the above table like.

id Hospital Doctor Patient
 1   A       D1      P11

 3   A       D2      P21

 2   B       D6      P61

 5   B       D7      P71

I am very sorry to repost this question.

From stackoverflow

• Try something like:

  select Id,Hospital,Doctor,Patient
    from table
    where Id in (select max(t.Id) from table t group by t.Hospital,t.Doctor)
    order by Hospital,Doctor;
  

  Giridhar : Your answer is accurate sweet and short.Thank you

• SELECT  m.*
  FROM    (
          SELECT  (
                  SELECT  id
                  FROM    mytable mi
                  WHERE   mi.hospital = md.hospital
                          AND mi.doctor = md.doctor
                  LIMIT 1
                  ) AS first_patient
          FROM    (
                  SELECT  DISTINCT hospital, doctor
                  FROM    mytable
                  ) md
          ) mo, mytable m
  WHERE   m.id = mo.first_patient
  

  Giridhar : This script says that right paranthsis is missing at SELECT ( SELECT id FROM mytable mi WHERE mi.hospital = md.hospital AND mi.doctor = md.doctor LIMIT 1 ) AS first_patient

• You might look at breaking things into three tables: Hospitals (with Primary Key id, and the Hospital field), Doctors (with someother PK, a Foreign Key of Hospitals, and the Doctor field) and Patients (with someother PK, a Foreign Key of Doctors, and the Patient field). Then your statement would look something like:

  SELECT H.Id, H.Hospital, D.Doctor, Max(P.Patient)
  FROM Hospitals H
  INNER JOIN Doctors D ON H.Hospital = D.Hospital
  INNER JOIN Patients P ON D.Doctor = P.Doctor
  ORDER BY Hospital, Doctor
  

  Giridhar : I dont have the control on table.

  Lance Roberts : OK, then you'll have to use the other answers.