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 3 13413 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.
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;
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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
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
|
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...
|
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...
|
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...
|
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...
| |
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
|
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
|
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.
|
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 " & _
...
|
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
|
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...
| |
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.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
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...
|
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...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
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 the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
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
|
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...
| |