Re: Query question

[SGreen]

"Jeff McKeon" <jmckeon@stripped> wrote on 04/25/2005 03:00:55 PM:

> I have a table that contains records that link back to a main talbe in a
> many to one configuration linked by table1.id = table2.parentid
> 
> Table1 (one)
> Table2 (many)
> 
> I want to pull the latest records from table2 for each record in table1
> where certain criteria applie.
> 
> So, if record 100 in table1 links to 5 corresponding records in table2,
> I want to pull the latest record from table2 where table2.parentid = 100
> and table2.user not like 'john'
> 
> There is a datestamp field in table2.
> 
> I just can't figure out how to do this.
> 
> Thanks,
> 
> Jeff
> 

This is a FAQ. What you have is the "groupwise maximum" problem described 
here:

http://dev.mysql.com/doc/mysql/en/example-maximum-column-group-row.html

Except in your case you are not looking for max price for a dealer, you 
are looking for the latest date for a given parentid. Let us know if you 
need more details and someone on the list will be happy to help!! :-)

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine