Selecting with JOIN, GROUP BY and MAX

[simon blenkinsop]

I have a table containing the 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 associated 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:

SELECT
u.unit_id,
u.description,
u.lots_of_other_stuff,
l.temp,
l.date
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
__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/my***********...ie.nctu.edu.tw