By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,288 Members | 3,027 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,288 IT Pros & Developers. It's quick & easy.

Selecting with JOIN, GROUP BY and MAX

P: n/a
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
Jul 19 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.