Re: Limit within groups
On Tue, Jan 6, 2009 at 3:13 PM, Jerry Schwartz jschwa...@the-infoshop.com wrote: Each account has multiple customers, and each customer has multiple sales. I want to get the top 20 customers for each account. http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/ Keep reading, it talks about top N per group later on in the post. Select the top N rows from each group -- Baron Schwartz, Director of Consulting, Percona Inc. Our Blog: http://www.mysqlperformanceblog.com/ Our Services: http://www.percona.com/services.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Limit within groups
-Original Message- From: baron.schwa...@gmail.com [mailto:baron.schwa...@gmail.com] On Behalf Of Baron Schwartz Sent: Wednesday, January 07, 2009 9:54 AM To: Jerry Schwartz Cc: mysql@lists.mysql.com Subject: Re: Limit within groups On Tue, Jan 6, 2009 at 3:13 PM, Jerry Schwartz jschwa...@the-infoshop.com wrote: Each account has multiple customers, and each customer has multiple sales. I want to get the top 20 customers for each account. http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax- row-per-group-in-sql/ Keep reading, it talks about top N per group later on in the post. Select the top N rows from each group [JS] Thanks for the pointer. Unless I'm misunderstanding them, none of the techniques look very promising. I need the top 20 customers for each of 15 or more accounts, but each customer has many records so I need to rank them by sum() for each customer and then select the first 20 for each account. It looks as though I'd have build a separate query for each account, and then UNION them all together. I'm trying to avoid that, especially since some of the account Ids are actually Japanese words. What I really need is some kind of for each functionality. My goal is tantalizingly just out of reach. -- Baron Schwartz, Director of Consulting, Percona Inc. Our Blog: http://www.mysqlperformanceblog.com/ Our Services: http://www.percona.com/services.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Limit within groups
On Wed, Jan 7, 2009 at 1:48 PM, Jerry Schwartz jschwa...@the-infoshop.com wrote: -Original Message- From: baron.schwa...@gmail.com [mailto:baron.schwa...@gmail.com] On Behalf Of Baron Schwartz Sent: Wednesday, January 07, 2009 9:54 AM To: Jerry Schwartz Cc: mysql@lists.mysql.com Subject: Re: Limit within groups On Tue, Jan 6, 2009 at 3:13 PM, Jerry Schwartz jschwa...@the-infoshop.com wrote: Each account has multiple customers, and each customer has multiple sales. I want to get the top 20 customers for each account. http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax- row-per-group-in-sql/ Keep reading, it talks about top N per group later on in the post. Select the top N rows from each group [JS] Thanks for the pointer. Unless I'm misunderstanding them, none of the techniques look very promising. I need the top 20 customers for each of 15 or more accounts, but each customer has many records so I need to rank them by sum() for each customer and then select the first 20 for each account. This is one way to do what you need, depending on your data (doesn't always work): select type, variety, price from fruits where ( select count(*) from fruits as f where f.type = fruits.type and f.price fruits.price ) = 2; Vary the 2 to get what you want. -- Baron Schwartz, Director of Consulting, Percona Inc. Our Blog: http://www.mysqlperformanceblog.com/ Our Services: http://www.percona.com/services.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
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.. Regards Phil On Tue, Jan 6, 2009 at 3:13 PM, Jerry Schwartz jschwa...@the-infoshop.comwrote: 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
RE: Limit within groups
-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.comwrote: 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
Re: Limit within groups
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.comwrote: -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.comwrote: 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
