Geek asks and answers

How do you limit a group to rows in a mysql query?

There are other questions that are similar to sound, but not like that. I have a query that returns a group of rows with a group, and I want to apply the limit to the general group by rows, and not to the common rows used to create 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 GUIDELINES GROUP WHO HAVE ALREADY WIN THE MY LIMIT.

TYPE       SIZE       ROWS
---------------------------    
Circle     Large      4
Square     Large      1
Oval       Large      1
Triangle   Large      2
Star       Large      1

^^^^ HERE WHAT I WANT IS THE LIMIT APPLICABLE TO GROUPS.

There must be some kind of subquery or something that I can do here, but I don't understand it.

Thank.

+5
source share
3 answers

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 be applied after GROUP BY, so I do not understand the problem.

+4
source 
SELECT * FROM (
  SELECT id, color, size, type FROM clothes WHERE size = 'large' GROUP BY type 
) AS baseview LIMIT 0, 25
+3
source

, ?

select color, size, type, count(id) from clothes where size = 'large' group by size, type, color;

I am new to sql programming, but this should return the following: this way you use 1 line for each combination of colors / sizes and types and get the number after it.

Red big shirt 4

Green Big Shirt 6

Green Pants 2

etc.

0
source

More articles:


All Articles