Yes you're right. I went off on a complete tangent with my thoughts on this and it does not do what you wanted at all...Sorry!
I can't think of any way to do this via sql only as it's almost a group_limit_by that you'd want. It seems much more like a procedural call, so I'd expect you'd need some form of cursor wrapped around the group by to get the top20 for each account. Sorry about that! Phil On Tue, Jan 6, 2009 at 3:59 PM, Jerry Schwartz <jschwa...@the-infoshop.com>wrote: > > > >-----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 > > > > -- Distributed Computing stats http://stats.free-dc.org
