I have a table containing fields unit_id, temperature and date. This
has data for around 20 units, with many 1,000's of records for each.
Further information on each unit is in another table with just 1 entry
per unit.
I need to select the most recent record for each unit together with
its extra data. I cannot see how to do this in a single select. At
present I sort by date DESC and group by unit_id then my perl script
discards all but the 1st record for each unit.
The select is:
$sqlquery = qq[SELECT
u.unit_id,
u.description,
u.owner,
u.location,
u.contents,
u.min_temp,
u.min_temp_delay,
u.max_temp,
u.max_temp_delay,
u.silence_delay,
u.email,
u.sms,
l.temp,
l.date,
u.low_alarm,
u.hi_alarm,
u.silent_alarm,
u.alarm_state_set
FROM units u, log l WHERE (u.unit_id = l.unit_id)
ORDER by u.unit_id, l.date DESC];
This involves a lot of (perl) processing and wasted mysql retrieval,
there must be a better way.
Any ideas ?
Thanks
Steve