List:General Discussion« Previous MessageNext Message »
From:SGreen Date:December 21 2005 4:11pm
Subject:Re: Filtering join
View as plain text  
I can't take it any longer. Come on list this is an easy one!! We have 
only had this question asked about once every other week this year.  Shame 
on you lurkers who knew the answer but didn't kick in.  8-( 

Eris,
What you are looking for can be called the "group wise maximum" because 
you want the row with the max value for a particular group. In your case 
(using your second example) you want the row with the maximum `no` for 
each `id` value

The FINE MANUAL has three ways to write queries to solve this problem:
http://dev.mysql.com/doc/refman/4.1/en/example-maximum-column-group-row.html

Let us know if you have problems translating the documentation to fit your 
needs. (for shame, for shame!)

Merry Christmas!

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine



"Eris Ristemena" <eris@stripped> wrote on 12/21/2005 11:01:59 AM:

> Ok, i change the table to make it clear.
> 
> I have two tables, person and expense
> person
> id        name
> 1         james
> 2         michael
> 
> expense
> no        id        exp
> 1          1        2000
> 2          2        1000
> 3          1          500
> 
> where expense.no is an autoincrement column.
> 
> how can i get the last expense of each people in table person? so the 
result
> should be:
> 
> id      name      no        exp
> 1       james      3         500
> 2       michael    2       1000
> 
> thanks in advance
> 
> 
> 
> 
> ----- Original Message -----
> From: "Peter Brawley" <peter.brawley@stripped>
> To: "Eris Ristemena" <eris@stripped>
> Cc: <mysql@stripped>
> Sent: Wednesday, December 21, 2005 10:43 PM
> Subject: Re: Filtering join
> 
> 
> > Eris,
> >
> >  >...what i need is a distinct t1.id with maximum t2.no, so that the
> result
> >  >should be like this:
> >  >id name no id cust
> >  >1 a 2 1 y
> >  >2 b null null null
> >  >3 c null null null
> >
> > To get the maximum t2.no value for each t1.id value, try ...
> >
> >   SELECT
> >     t1.id,
> >     t1.name,
> >     t2.id,
> >     MAX(t2.no)
> >   FROM t1 LEFT JOIN t2 USING (id)
> >   GROUP BY t1.id;
> >
> > but because of the MAX() / GROUP BY aggregation, adding t2.cust to the
> > query will not give you the t2.cust values that go with t2.no values.
> >
> > PB
> >
> > -----
> >
> > Eris Ristemena wrote:
> >
> > >hi all,
> > >
> > >i have this small problem. I hope someone can help me out here.
> > >
> > >i have two table with one-to-many relations,
> > >t1
> > >id   name
> > >1      a
> > >2      b
> > >3      c
> > >
> > >t2
> > >no   id   cust
> > >1      1     x
> > >2      1     y
> > >
> > >using join statement like this:
> > >select * from t1 left join t2 using (id)
> > >
> > >i get this result:
> > >id    name   no   id   cust
> > >1        a       1     1     x
> > >1        a       2     1     y
> > >2        b      null  null  null
> > >3        c      null  null  null
> > >
> > >but what i need is a distinct t1.id with maximum t2.no, so that the
> result
> > >should be like this:
> > >id    name   no   id   cust
> > >1        a       2     1     y
> > >2        b      null  null  null
> > >3        c      null  null  null
> > >
> > >Can someone help me how? group by seem doesn't work.
> > >
> > >regards,
> > >-ers
> > >
> > >
> > >
> > >
> >
> >
> > --
> > No virus found in this outgoing message.
> > Checked by AVG Free Edition.
> > Version: 7.1.371 / Virus Database: 267.14.3/209 - Release Date: 
12/21/2005
> >
> >
> >
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
> 

Thread
Do I need to tweak my server variables for this SELECT statement?Grant Giddens21 Dec
Re: Do I need to tweak my server variables for this SELECTstatement?James Harvard21 Dec
  • Re: Do I need to tweak my server variables for this SELECT statement?Grant Giddens21 Dec
    • Re: Do I need to tweak my server variables for this SELECT statement?Hank21 Dec
      • Re: Do I need to tweak my server variables for this SELECT statement?Grant Giddens21 Dec
        • Re: Do I need to tweak my server variables for this SELECT statement?Hank21 Dec
          • Re: Do I need to tweak my server variables for this SELECT statement?Grant Giddens21 Dec
          • Re: Do I need to tweak my server variables for this SELECT statement?Grant Giddens21 Dec
            • Re: Do I need to tweak my server variables for this SELECT statement?Hank23 Dec
              • Re: Do I need to tweak my server variables for this SELECT statement?Grant Giddens23 Dec
                • Re: Do I need to tweak my server variables for this SELECTstatement?James Harvard23 Dec
                  • Re: Do I need to tweak my server variables for this SELECT statement?Grant Giddens23 Dec
                    • Re: Do I need to tweak my server variables for this SELECT statement?Hank24 Dec
    • Filtering joinEris Ristemena21 Dec
      • Re: Filtering joinPeter Brawley21 Dec
      • Re: Filtering joinGleb Paharenko21 Dec
    • Re: Do I need to tweak my server variables for this SELECTstatement?James Harvard21 Dec
    • Re: Filtering joinEris Ristemena21 Dec
    • Re: Filtering joinEris Ristemena21 Dec
      • Re: Filtering joinSGreen21 Dec
        • Re: Filtering joinEris Ristemena21 Dec
          • Re: Filtering joinSGreen21 Dec
            • Re: Filtering joinEris Ristemena21 Dec
Re: Do I need to tweak my server variables for this SELECT statement?Grant Giddens21 Dec
  • Re: Do I need to tweak my server variables for this SELECT statement?Hank21 Dec
    • Re: Do I need to tweak my server variables for this SELECT statement?SGreen21 Dec