Re: Selecting with JOIN, GROUP BY and MAX

[John Larsen]

Geez, do I have to do all your work for you, :)
First thing I can think of is,
Ok this makes no sence you have a units table with unit_id as its main 
descriptor, but there can be several cases with the same unit_id but you 
want the one with the max date. Date on the other hand is stored in 
another table and references date to unit, so how in the world can you 
specify you want the max date record of
many unit_id records  in the units table when there is no identifying 
mark in said table to say which one matches with the max date.. or am I 
missing something?



simon blenkinsop wrote:

I don't think these would do what I want, I need the
record with the max date *for each unit*. Each of
these would just select the one unit that had the max
date.
Steve

--- John Larsen <[EMAIL PROTECTED]> wrote:
 

Try just doing a WHERE to select the record with the
max date.
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 and l.date=(SELECT
MAX(log.date) FROM log)
						   ^^^
						you may be able to replace log with l here
					 but I'm now sure if subqueries use aliases
from the parent.

Or you can just  use your existing query as the top
record is the one 
you want to keep and tell it to limit output to 1
record.

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
LIMIT 1



The first solution would give you mulitple outputs
if you had multiple 
lines with the same maximum date, and the second
will only give you the 
first.

simon blenkinsop wrote:

   

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



     

   



__________________________________
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/[EMAIL PROTECTED]