Ok, that's easy, i see. But now how can i also get who hasn't done the expense?
person id name 1 james 2 michael 3 jack expense no id exp 1 1 2000 2 2 1000 3 1 500 so that the result should be: id name no exp 1 james 3 500 2 michael 2 1000 3 jack null null ----- Original Message ----- From: <[EMAIL PROTECTED]> To: "Eris Ristemena" <[EMAIL PROTECTED]> Cc: <mysql@lists.mysql.com> Sent: Wednesday, December 21, 2005 11:11 PM Subject: Re: Filtering join > 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] > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
