EXTRACT ONLY THE LATEST VALUE

[VirtualOdin]

I have two tables, 'person' with all the static data and 'event' with multiple records for each bit of dynamic data associated with rows in 'person' through 'person_id'.  So I can extract all the event data with something like

 

SELECT * FROM `person` LEFT JOIN `event` ON ( `person`.`id` = `event`.`person_id`) WHERE ...

 

And from that result I can work out in PHP which is the latest event, which for this purpose is all I need.  But I feel sure it should be possible to do all that inside the query and pull out only the latest 'event' data and the person material.  Is it possible?

 

As always, thanks in inticipation...

[Mchl]

What is 'latest' in this case? Is there any timestamp field in the table?

[VirtualOdin]

Yes, sorry should have said, there is a DATE field in the 'event' table

[kickstart]

Hi

 

Try something like this:-

 

SELECT * 
FROM `person` a
LEFT JOIN (SELECT person_id, max(eventDate) AS maxEventDate FROM `event` GROUP BY person_id) b ON a.id = `b.person_id
JOIN `event` c ON b.person_id = c.person_id AND b.maxEventDate = c.eventDate
WHERE ...

 

All the best

 

Keith

[VirtualOdin]

Thanks very much.  It works.

[VirtualOdin]

That's great for one additional table.  What I should have thought through is that I have a four tables like 'event' on each of which I need to do the same.  I'd appreciate any advice on how to do it with more than one, so for each person I pull out the latest value from the other tables organised like event, ie with a date and some date related to the person  (max dates will vary of course).  I can't quite see what to replicate to end up with all the latest data from the 4 tables.

[VirtualOdin]

So trial and error has produced this for two tables

 

SELECT * FROM `person` a 
LEFT JOIN (SELECT `person_id`, MAX(`range`.`date`) AS maxRangeDate FROM `range` GROUP BY `person_id`) b 
ON (`a`.`id` = `b`.`person_id`) 
LEFT JOIN (SELECT `person_id`, MAX(`assignment`.`date`) AS maxAssignmentDate FROM `assignment` GROUP BY `person_id`) d 
ON (`a`.`id` = `d`.`person_id`) 
JOIN `range` c 
ON ( `b`.`person_id` = `c`.`person_id` AND `b`.`maxRangeDate` = `c`.`date`) 
JOIN `assignment` e 
ON ( `b`.`person_id` = `e`.`person_id` AND `d`.`maxAssignmentDate` = `e`.`date` ) 
WHERE `a`.`departure` = '0000-00-00' 

 

But it is taking quite a time on no data to speak of.

[kickstart]

Hi

 

Looks a fair enough way to do it. You do not need that many back tics (and I would recommend avoiding using any column names that are reserved words in SQL).

 

You have one minor typo:-

 

SELECT * FROM person a

LEFT JOIN (SELECT person_id, MAX(`range`.`date`) AS maxRangeDate FROM range GROUP BY person_id) b

ON (a.id = b.person_id)

LEFT JOIN (SELECT`person_id, MAX(`assignment`.`date`) AS maxAssignmentDate FROM assignment GROUP BY person_id) d

ON (a.id = d.person_id)

JOIN range c

ON ( b.person_id = c.person_id AND b.maxRangeDate = `c`.`date`)

JOIN assignment e

ON ( d.person_id = e.person_id AND d.maxAssignmentDate = `e`.`date` )

WHERE a.departure = '0000-00-00'

 

After that I would suggest you check the indexing on those tables.

 

All the best

 

Keith