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
