Using GROUP BY in MySQL Join Table

Question

i have problem using JOIN between two tables, Here is my table :

patient table

........................................
uid | no_mr     | name
........................................
1   | 1101      | Eko
2   | 1102      | John Doe

visit table

..............................................
uid | vcode         | patient_id
..............................................
1   | V-20130725143528  | 1
2   | V-20130726132308  | 2
3   | V-20130726142907  | 2
4   | V-20130726144436  | 1 

How to display patient data based on visit table, and sorted in descending order by visit.uid.

Here is my query,

SELECT patient . * , visit.uid
FROM patient
LEFT JOIN visit ON patient.uid = visit.patient_id
WHERE patient.uid >1
GROUP BY patient.no_mr
ORDER BY visit.uid DESC 
LIMIT 0 , 10

When i run that query, i have this value.

.......................................
uid | no_mr     | name
.......................................
2   | 1102      | John Doe
1   | 1101      | Eko

I want like this, display the patient data that have new visit data.

.......................................
uid | no_mr     | name
.......................................
1   | 1101      | Eko
2   | 1102      | John Doe

Please help, thank you..

Solution

Try this

SELECT q.* FROM (
SELECT patient . * , visit.uid AS visit_uid
FROM patient
LEFT JOIN visit ON patient.uid = visit.patient_id
ORDER BY visit_uid DESC 

) q GROUP BY q.uid ORDER BY q.visit_uid DESC 

LIMIT 0 , 10

Fiddle

Comments

[Fandy Akhmad]

Still not works when i have more than two patients and visit data. Here is my FIDDLE