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

```
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