Query with MAX() and LEFT JOIN

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
Hello Everyone,

I have been searching around for an answer to this question with no
luck.  I hope that some of you may have a couple of good ideas I could

I am running MySQL 4.1.20.

In one of my databases, I have two tables: patients, visits.

| id | name | dob |

| id | visitId | visitDate |

Each patient has several records in the visits table.  The two are
linked by the "id" unique identifier.

I am trying to retrieve the visitId of each patient's most recent
visit. Here is what I have been trying so far.

SELECT patients.id, patients.name, patients.dob, visits.visitId,
FROM patients
LEFT JOIN visits ON patients.id = visits.id
GROUP BY patients.id

The problem is that with this statement, the "visitId" that is
returned, does not correspond to the highest visit date.  So the query
pulls the highest visit date as a result of the MAX function, but the
visitId it returns does not belong to the record of this same visit.

I have a feeling that I must be missing something simple/obvious.

Any ideas?
Thanks for all of the help in advance.

Re: Query with MAX() and LEFT JOIN

torpecool@yahoo.com wrote:
Quoted text here. Click to load it

Answers to this kind of question have a habit of containing statements
like 'select something for which there is no other something having a
greater/lesser value' and they usually look like this:

SELECT v . *
FROM visits v
LEFT JOIN visits v2 ON v.id = v2.id
AND v.visitId <> v2.visitId
AND v.visitDate < v2.visitDate
WHERE v2.visitDate IS NULL;

The missing 'patients' part of this query has been left as an exercise
for the reader.

Re: Query with MAX() and LEFT JOIN

Wow... I'm not sure I understand what's happening there, but I tried it
and it works.  Thanks for the help.  This does exactly what I was
looking for.  Awesome.

Quoted text here. Click to load it

Re: Query with MAX() and LEFT JOIN

torpecool@yahoo.com wrote:
Quoted text here. Click to load it

No worries :-)

I'm not sure I really understand it either, but it seems to work.

Incidentally, if you look around the NGs I'm sure you'll find other
examples similar to this - including (the potentially very useful) ones
that let you select the latest two visits for each. If interested,
maybe try googling the groups for 'Top N', 'Having'  and 'group by'. I
think I might even have submitted a solution like that quite recently.

Site Timeline