Query with MAX() and LEFT JOIN

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
try.

I am running MySQL 4.1.20.

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

Patients:
| id | name | dob |

Visits:
| 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,
MAX(visit.visitDate)
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.
Andy

Re: Query with MAX() and LEFT JOIN

torpecool@yahoo.com wrote:
> 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
> try.
>
> I am running MySQL 4.1.20.
>
> In one of my databases, I have two tables: patients, visits.
>
> Patients:
> | id | name | dob |
>
> Visits:
> | 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,
> MAX(visit.visitDate)
> 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.
> Andy

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.


> 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;

Re: Query with MAX() and LEFT JOIN

torpecool@yahoo.com wrote:
> 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.
>
>
> > 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;

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.