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.. 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
