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 thanks in advance ----- Original Message ----- From: "Peter Brawley" <[EMAIL PROTECTED]> To: "Eris Ristemena" <[EMAIL PROTECTED]> Cc: <mysql@lists.mysql.com> 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/[EMAIL PROTECTED]
