How do you limit the group by rows in an mysql query?
mysql
Solution
This works for me:
SELECT type, size, COUNT(*) AS rows
FROM clothes
WHERE size = 'large'
GROUP BY type
LIMIT 0, 5
Results in:
type size rows
------------------------
Circle Large 4
Oval Large 1
Square Large 1
Star Large 1
Triangle Large 2
LIMIT should get applied after GROUP BY, so I don't understand the issue.
Problem
There are other questions on here that sound similar but are not. I have a query that returns a bunch of rows with group by and I want to apply a limit to the total group by rows, not the total rows used to create the groups. ``` ID TYPE COLOR SIZE ---------------------------------------- 1 Circle Blue Large 2 Circle Red Large 3 Square Green Large 4 Circle Purple Large 5 Circle Blue Small 6 Circle Yellow Medium 7 Circle Black Large 8 Oval Blue Large 9 Circle Gray Small 10 Triangle Black Large 11 Star Green Large 12 Triangle Purple Large SELECT size, type FROM clothes WHERE size = 'large' GROUP BY type LIMIT 0, 5 TYPE SIZE ROWS --------------------------- Circle Large 4 Square Large 1 ``` ^^^^ 2 GROUP BY ROWS THAT HAVE ALREADY EXHAUSTED MY LIMIT ``` TYPE SIZE ROWS --------------------------- Circle Large 4 Square Large 1 Oval Large 1 Triangle Large 2 Star Large 1 ``` ^^^^ HERE'S WHAT I WANT, LIMIT APPLIED TO THE GROUPS There must be some subquery or something I can do here, but I'm not figuring it out. Thanks.