473,580 Members | 2,960 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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.visit Date)
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 13411

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.visit Date)
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
2356
by: Wanny | last post by:
Hi There, I can't seem to see what's wrong with the query below DELETE FROM Users_Details UD1 WHERE UD1.UserID = ( SELECT TOP 1 UD2.UserID FROM Users_Details UD2 WHERE UD1.useremail = UD2.useremail
4
2651
by: Orion | last post by:
Hi, This is kind of last minute, I have a day and a half left to figure this out. I'm working on a project using ms-sqlserver. We are creating a ticket sales system, as part of the system, I need to be able to do a search for specific tickets withing price ranges, different locations within the theaters, etc. etc. My problem is in the...
5
8205
by: Todd | last post by:
Data related to the query I'm working on is structured such that TableA and TableB are 1-many(optional). If an item on TableA has children on TableB, I need to use the Max(tstamp) from Table B in a condition, otherwise I need to use a tstamp from TableA (note:there are additional tables and conditions for this query, but this problem is...
5
1673
by: Bob Stearns | last post by:
When I run the following query with the two sections commented out, the response time is between 1 an 2 seconds; with the first indicated section enabled, the response goes up to 15 seconds even though t1.bh_disposal_code IS NOT NULL in only one row; with the second section enabled, it goes up to 592 seconds, even though t1.mating is NULL in...
2
1954
by: John Ortt | last post by:
Hi All, I have a database in Access 97 which works fine but our company is converting from NT4 to XP Pro and in the process changing Office 97 for Office 2003. I have tried to convert the 97 database into Access 2003 but the Autoexec macro falls over on the query below. I have tried running it separayely and the computer just hangs. I...
1
1392
by: mithril | last post by:
I cannot get my head around this query... I think I need a nested query but here's the problem. I promise i've exerted my meager brain power on this problem & ask this as a last resot! 3 Tables PatientTable PatActive, PatID ProfileTable
2
1648
by: AJ | last post by:
Hi all, I have this monster query (at least i think it is). SELECT c.ID, c.Company_Name, p., 1 As QueryNbr FROM (Company AS c LEFT JOIN Sale AS s ON c.ID = s.Company_ID) LEFT JOIN Package AS p ON s.Package_ID = p.ID
2
1937
by: scolivas | last post by:
Is there a way to automate this process? I have a query that finds the "Drop Offs" by doing an unmatch query against the hard table and the live table. I want to automatically pull these "Drop Offs" out of the Hard table. my sql for the query I am running follows: 1st Query - pulls the data from the live tables.
1
1159
by: devgupta01 | last post by:
SELECT NVL(ManagerID,'Other') ManagerID, NVL(Manager,'Other') Manager, MAX(ManagerOpenACount) ManagerOpenACount, MAX(ManagerOpenUACount) ManagerOpenUACount, MAX(ManagerCloseCount) ManagerCloseCount, MAX(ManagerCount) ManagerCount, MAX(ManagerRejectCount) ManagerRejectCount, MAX(ManagerPendingCount) ManagerPendingCount, MAX(ManagerReopenCount)...
11
2633
by: lenygold via DBMonster.com | last post by:
Hi everybody! This query is supposed to count consecutive years from the current year without OLAP. Input Table: ID DateCol 1 02/01/2006 1 01/01/2006 1 01/01/2005
0
7854
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
8132
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
1
7878
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
8157
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6533
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
0
5349
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3790
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3806
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
0
1118
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.