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

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