List:General Discussion« Previous MessageNext Message »
From:SGreen Date:April 25 2005 7:13pm
Subject:Re: Query question
View as plain text  
"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

Thread
Query questionJeff McKeon25 Apr
  • Re: Query questionSGreen25 Apr
  • RE: Query questionmathias fatene25 Apr
    • RE: Query questionSGreen25 Apr
      • RE: Query questionmathias fatene25 Apr
  • Re: Query questionPeter Brawley25 Apr
    • RE: Query questionmathias fatene25 Apr
RE: Query questionJeff McKeon25 Apr
  • Re: Query questionPeter Brawley25 Apr
    • RE: Query questionmathias fatene25 Apr
      • Re: Query questionPeter Brawley25 Apr
      • RE: Query questionSGreen25 Apr
        • RE: Query questionmathias fatene26 Apr
          • Re: Query questionMartijn Tonies26 Apr
            • RE: Query questionmathias fatene26 Apr
              • Re: Query questionChris Ramsay26 Apr
                • Re: Query questionmfatene26 Apr
  • Re: Query questionPeter Brawley25 Apr
  • RE: Query questionSGreen25 Apr
RE: Query questionJeff McKeon25 Apr
  • Re: Query questionPeter Brawley25 Apr
RE: Query questionJeff McKeon25 Apr
  • Re: Query questionPeter Brawley25 Apr
RE: Query questionJeff McKeon25 Apr