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

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 2

I've had some luck with using numbered rows:

set @type = '';
set @num = 0;

select
  items.*,
  @num := if(@type = item_type, @num + 1, 1) as dummy_1,
  @type := item_type as dummy_2,
  @num as row_number
from items
group by
  item_type,
  row_number
having row_number < 3;

This will give you 2 results per item_type. (One gotcha: make sure you re-run the first two set statements otherwise your row numbers will steadily get higher and higher and the row_number < 3 restriction won't work.

I pieced this together from a couple of posts which have been linked in other answers on SO.