MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:simon blenkinsop Date:July 22 2003 1:21pm
Subject:Selecting with JOIN, GROUP BY and MAX
View as plain text  
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
Thread
Selecting with JOIN, GROUP BY and MAXsimon blenkinsop22 Jul
Re: Selecting with JOIN, GROUP BY and MAXJohn Larsen22 Jul