Michael Sole
asked on
Limit a join to one newest row
I have a table of patients and a table of next activity dates. Each time a next activity is scheduled it adds a row with the date and the patient ID. I want to retrieve a list of patients and their newest next activity date.
So example table structures would be
Patients:
id, name
1, John Doe
2, Jane Smith
Next Activity
id, pid, nextActivity
1,1,11/12/2012
2,1,3/15/2011
3,2,11/1/2011
4,2,4/1/2011
Example output:
ID Name Next Activity
1 John Doe 11/12/2012
2 Jane Smith 11/1/2011
This is in mysql 5 access via PHP, but I don't really need help with the code just the select statement. I should really know this but I am blanking at the moment.
So example table structures would be
Patients:
id, name
1, John Doe
2, Jane Smith
Next Activity
id, pid, nextActivity
1,1,11/12/2012
2,1,3/15/2011
3,2,11/1/2011
4,2,4/1/2011
Example output:
ID Name Next Activity
1 John Doe 11/12/2012
2 Jane Smith 11/1/2011
This is in mysql 5 access via PHP, but I don't really need help with the code just the select statement. I should really know this but I am blanking at the moment.
try
select a.id, a.name, b.mnextactivity
from Patients a
inner join (select pid, max(nextActivity) mnextactivity from NextActivitytable group by pid) b on a.id = b.pid
order by a.name
select a.id, a.name, b.mnextactivity
from Patients a
inner join (select pid, max(nextActivity) mnextactivity from NextActivitytable group by pid) b on a.id = b.pid
order by a.name
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
oops, forgot the name sorting there:
select a.id, a.name, max(b.nextactivity) as newestactivity
from Patients a
inner join NextActivitytable b on a.id = b.pid
group by a.id, a.name
order by a.name
select a.id, a.name, max(b.nextactivity) as newestactivity
from Patients a
inner join NextActivitytable b on a.id = b.pid
group by a.id, a.name
order by a.name
ASKER
Yeah I would of figured that part out. I hadn't even thought of using max, great solution!
I was thinking of using group by and limit in some way but was coming up empty.
Thank you :)
I was thinking of using group by and limit in some way but was coming up empty.
Thank you :)
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
ASKER