I can't take it any longer. Come on list this is an easy one!! We have only had this question asked about once every other week this year. Shame on you lurkers who knew the answer but didn't kick in. 8-(
Eris, What you are looking for can be called the "group wise maximum" because you want the row with the max value for a particular group. In your case (using your second example) you want the row with the maximum `no` for each `id` value The FINE MANUAL has three ways to write queries to solve this problem: http://dev.mysql.com/doc/refman/4.1/en/example-maximum-column-group-row.html Let us know if you have problems translating the documentation to fit your needs. (for shame, for shame!) Merry Christmas! Shawn Green Database Administrator Unimin Corporation - Spruce Pine "Eris Ristemena" <[EMAIL PROTECTED]> wrote on 12/21/2005 11:01:59 AM: > 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] >
