473,847 Members | 1,732 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 13424

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
2369
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
2661
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 search one of the criteria is to search for a group of seats together. For example let's say...
5
8221
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 based around these 2). I attempted having TableB (as B) "left outer joined" to TableA, and a condition...
5
1686
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 all the rows chosen. Why should the (supposedly never executed) scalar subqueries cost so much? It...
2
1971
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 realise there is a lot of code below, but I'm hoping somebody may know what they are looking for...
1
1401
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
1660
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
1958
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
1167
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) ManagerReopenCount, MAX(ManagerCPRCount) ManagerCPRCount, CONCAT('',ROUND(MAX(Pct),2)) Pct FROM " & _ ...
11
2675
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
9887
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 usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9730
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10648
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10341
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 choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9485
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 launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
7056
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 into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
1
4529
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4119
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3164
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 effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.