mysql limit inside group?

Question

I want to limit the size of records inside a group, and here is my trial, how to do it right?

mysql> select * from accounts limit 5 group by type;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'group by type' at line 1

Answer 1

Group by is used for aggregate functions (sums, averages...)

Is allows you to split the aggregate result into groups. You have not used one of these functions.

Answer 2

I am not sure you can use a limit in the group by. You can probably use it if your group by is a sub select that returns one row/value. For example:

select * from foo order by (select foo2.id from foo2 limit 1)

I am just guessing this would work.

Answer 3

Try placing the LIMIT clause after the GROUP BY clause.

EDIT: Try this:

SELECT * 
FROM accounts a1
WHERE 5 > 
(
   SELECT COUNT(*)
   FROM accounts a2
   WHERE a2.type = a1.type
   AND a2.balance > a1.balance
)

This returns at most 5 accounts of each type with the biggest balances.

Answer 4

select * from accounts group by type limit 5;

should do the trick - even though I doubt that the result will yield something useable (selecting all columns and group by one column without any aggregation function).

Answer 5

It appears you want to limit the number of rows returned within each group of your overall result set... this is difficult to do in a way that scales well. One technique is to perform N joins on the same table with the conditions such that the only rows that match are the top/bottom N that you want.

this page may offer some additional insight into your solution... although returning the top 5 in each group is going to get ugly fast.

Answer 6

This will probably do the trick, although if type isn't indexed, it'll be sloooowwww. And even with one, it's not especially fast:

SELECT a.*
FROM accounts a
     LEFT JOIN accounts a2 ON (a2.type = a.type AND a2.id < a.id)
WHERE count(a2.id) < 5
GROUP BY a.id;

A better bet would be to just order the list by type and then use a loop at the business layer to remove the rows you don't want.

Answer 7

The point of an aggregate function (and the GROUP BY it requires) is to turn many rows into one row. So if you really just want the top 5 savings accounts and the top 5 chequing accounts and the top 5 USD accounts etc., what you need is more like this:

criteria: top 5 of particular account type by account_balance

SELECT account_type, account_balance FROM accounts WHERE account_type='savings' 
   ORDER BY account_balance DESC LIMIT 5
UNION
SELECT account_type, account_balance FROM accounts WHERE account_type='chequing' 
   ORDER BY account_balance DESC LIMIT 5
UNION
SELECT account_type, account_balance FROM accounts WHERE account_type='USD' 
   ORDER BY account_balance DESC LIMIT 5;

It's not pretty, but if you construct the SQL with a script then subbing in the account_types and concatenating together a query is straightforward.

Answer 8

I have the same problem, I'm looking for a function which limits the number of rows per group, something like a having() function but limiting the number of rows in the group. Difficult?