mysql limit inside group?
mysql, sql
Solution
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.
Problem
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