List:General Discussion« Previous MessageNext Message »
From:Eris Ristemena Date:December 21 2005 3:53pm
Subject:Re: Filtering join
View as plain text  
>
> 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.
>

thats the problem. GROUP BY will output unpredictable result if it is used
on not unique column.

What i meant here is actually not maximum of t2.no.

But the newest t2.cust, where t2.no is an auto increment column.




> 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