> > 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/[EMAIL PROTECTED] > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
