>-----Original Message----- >From: freedc....@gmail.com [mailto:freedc....@gmail.com] On Behalf Of >Phil >Sent: Tuesday, January 06, 2009 3:41 PM >To: Jerry Schwartz >Cc: mysql@lists.mysql.com >Subject: Re: Limit within groups > >How about something like > >select account,customer,max(total) from (select >account,customer,sum(sale_amount) as total from <tablename> group by >customer) as y group by account; > >Seems to work in my test case.. > [JS] This would return a record for each customer, wouldn't it? I don't see anything in there to limit the number of records returned for each account. >Regards > >Phil > > > >On Tue, Jan 6, 2009 at 3:13 PM, Jerry Schwartz ><jschwa...@the-infoshop.com>wrote: > >> Here's a bit of business that is baffling me, and probably shouldn't. >My >> table looks like this: >> >> >> >> account >> >> customer >> >> sale_amount >> >> >> >> Each account has multiple customers, and each customer has multiple >sales. >> I >> want to get the top 20 customers for each account. >> >> >> >> If I simply do "GROUP BY account, customer LIMIT 20", I'll get the >first 20 >> customers for the first account. If I try "GROUP BY account, customer >ORDER >> BY SUM(sale_amount) DESC LIMIT 20", I'll get the top 20 customers. >> >> >> >> What am I missing? >> >> >> >> Regards, >> >> >> >> Jerry Schwartz >> >> The Infoshop by Global Information Incorporated >> >> 195 Farmington Ave. >> >> Farmington, CT 06032 >> >> >> >> 860.674.8796 / FAX: 860.674.8341 >> >> >> >> <http://www.the-infoshop.com> www.the-infoshop.com >> >> <http://www.giiexpress.com> www.giiexpress.com >> >> www.etudes-marche.com >> >> >> >> > > >-- >Distributed Computing stats >http://stats.free-dc.org
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
