"Jeff McKeon" <[EMAIL PROTECTED]> 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
