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
