469,582 Members | 2,079 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,582 developers. It's quick & easy.

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

Jul 25 '06 #1
3 13139

to*******@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.

Jul 25 '06 #2
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;
Jul 26 '06 #3

to*******@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.

Jul 26 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Wanny | last post: by
4 posts views Thread by Orion | last post: by
5 posts views Thread by Bob Stearns | last post: by
2 posts views Thread by AJ | last post: by
11 posts views Thread by lenygold via DBMonster.com | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.