GROUP BY with multiple colums and LIMIT 2?

Question

I ve got the following data structure in my mysql Database:

+-------+----------+-------+------------+-------------+-------------+
| Color |  brand   | size  | otherstuff | otherstuff2 | otherstuff3 |
+-------+----------+-------+------------+-------------+-------------+
| red   | BMW      | small | 0,69       | 0,22        | 0,36        |
| red   | BMW      | big   | 0,48       | 0,00        | 0,13        |
| red   | Ford     | small | 0,42       | 0,33        | 0,81        |
| red   | BMW      | big   | 0,99       | 0,66        | 0,27        |
| red   | Ford     | big   | 0,30       | 0,44        | 0,23        |
| red   | Mercedes | small | 0,87       | 0,23        | 0,80        |
| red   | Mercedes | big   | 0,51       | 0,06        | 0,46        |
| blue  | BMW      | small | 0,07       | 0,53        | 0,99        |
| blue  | BMW      | small | 0,57       | 0,45        | 0,45        |
| blue  | BMW      | big   | 0,14       | 0,23        | 0,37        |
| blue  | Ford     | big   | 0,83       | 0,14        | 0,20        |
| blue  | Ford     | big   | 0,20       | 0,10        | 0,60        |
| blue  | Mercedes | small | 0,58       | 0,90        | 0,90        |
| blue  | BMW      | small | 0,12       | 0,41        | 0,40        |
| blue  | Mercedes | big   | 0,81       | 0,57        | 0,79        |
| blue  | Mercedes | big   | 0,95       | 0,36        | 0,70        |
| green | Ford     | big   | 0,09       | 0,56        | 0,90        |
| green | Ford     | small | 0,54       | 0,03        | 0,99        |
| green | Ford     | small | 0,86       | 0,58        | 0,12        |
| green | BMW      | small | 0,88       | 0,39        | 0,91        |
| green | BMW      | small | 0,39       | 0,92        | 0,50        |
| green | BMW      | big   | 0,11       | 0,62        | 0,23        |
+-------+----------+-------+------------+-------------+-------------+

I would like to receive per color and per brand one big size and one small size row - like this:

+-------+----------+-------+------------+-------------+-------------+
| Color |  brand   | size  | otherstuff | otherstuff2 | otherstuff3 |
+-------+----------+-------+------------+-------------+-------------+
| red   | BMW      | small | 0,69       | 0,22        | 0,36        |
| red   | BMW      | big   | 0,48       | 0,00        | 0,13        |
| red   | Ford     | small | 0,42       | 0,33        | 0,81        |
| red   | Ford     | big   | 0,30       | 0,44        | 0,23        |
| red   | Mercedes | small | 0,87       | 0,23        | 0,80        |
| red   | Mercedes | big   | 0,51       | 0,06        | 0,46        |
| blue  | BMW      | small | 0,57       | 0,45        | 0,45        |
| blue  | BMW      | big   | 0,14       | 0,23        | 0,37        |
| blue  | Ford     | big   | 0,20       | 0,10        | 0,60        |
| blue  | Mercedes | big   | 0,81       | 0,57        | 0,79        |
| blue  | Mercedes | small | 0,58       | 0,90        | 0,90        |
| green | Ford     | big   | 0,09       | 0,56        | 0,90        |
| green | Ford     | small | 0,54       | 0,03        | 0,99        |
| green | BMW      | small | 0,39       | 0,92        | 0,50        |
| green | BMW      | big   | 0,11       | 0,62        | 0,23        |
+-------+----------+-------+------------+-------------+-------------+

My SQL:

SELECT * FROM `table` GROUP BY color

What has my SQL to look like to receive a small and a big brand per color?

Do you care about the `otherstuff` values or can they be any value from that colour and size? — Nick, Apr 15 '20 at 07:58
You typically GROUP BY the same columns as you SELECT, except those who are arguments to set functions. — jarlh, Apr 15 '20 at 08:02
So, what you want to do is pick one row arbitrarily per color, brand, and size? Which MySQL version are you using? — Thorsten Kettner, Apr 15 '20 at 08:08
@Nick - Thanks that worked for me. (SELECT * FROM [table] GROUP BY color, brand, size ORDER BY color, brand, size;) — Ben, Apr 15 '20 at 08:25

score 2 · Accepted Answer · answered Apr 15 '20 at 08:28

2

To pick one row per color, brand, and size arbitrarily use ROW_NUMBER:

SELECT color, brand, size, otherstuff, otherstuff2, otherstuff3
FROM
(
  SELECT
    t.*,
    ROW_NUMBER() OVER (PARTITION BY color, brand, size ORDER BY otherstuff) AS rn
  FROM mytable t
) numbered
WHERE rn = 1
ORDER BY color, brand, size;

answered Apr 15 '20 at 08:28

Thorsten Kettner

89,309
7
49
73

This is working perfectly. Thank you so much. What do I ve to change if I want the max(otherstuff) from the row where size= big and the min(otherstuff) where size=small? @thorsten – Ben Apr 17 '20 at 08:15
This would no longer be picking rows arbitrarily, but looking for specific aggregation values. Sounds like a `GROUP BY` query with conditional aggregation. I suggest you look up "conditional aggregation" and try it. Ask this in a new separate request, if you cannot solve it yourself. – Thorsten Kettner Apr 17 '20 at 08:28

score 0 · Answer 2 · answered Apr 15 '20 at 08:27

0

Thanks @Nick and all others. This worked for me:

SELECT color, brand, size, otherstuff1, otherstuff2, otherstuff3 FROM [table] GROUP BY color, brand, size ORDER BY color, brand, size

Thank you

answered Apr 15 '20 at 08:27

Ben

73
7

2

This is invalid SQL, though. MySQL and MariaDB may let this slip, but they silently apply `ANY_VALUE` on the non-grouped columns (your otherstuff columns). Be aware that with this query a result row can contain `otherstuff1`, `otherstuff2`, `otherstuff3` picked from different rows (though this isn't likely to happen). As you seem to be lacking any experience with `GROUP BY` I recommend you read up on this and `SET sql_mode = 'ONLY_FULL_GROUP_BY';` to guide you until you are absolutely sure what you are doing (i.e. understand for instance what "functionally dependent" means). – Thorsten Kettner Apr 15 '20 at 08:40
Maybe reading this blog helps you understanding `GROUP BY`: https://rpbouman.blogspot.com/2014/09/mysql-575-group-by-respects-functional.html. It's about a change in MySQL some years ago concerning standard compliance by detection of functional dependence in aggregation queries. (MariaDB still lacks this enhancement, as to my knowledge.) I think this article explains very well what `GROUP BY` does and which columns can be selected when using this clause. – Thorsten Kettner Apr 15 '20 at 08:48
@ThorstenKettner - Thans for hints and links. – Ben Apr 15 '20 at 08:59
@Ben . . . This is broken SQL -- the `SELECT` and `GROUP BY` columns do not match. It is simply not guaranteed to do what you want. Thorsten's answer is correct (although it is not the only possible correct solution). – Gordon Linoff Apr 15 '20 at 11:20
Thanks guys. I will try @ThorstenKettner 's code later. – Ben Apr 15 '20 at 15:30

GROUP BY with multiple colums and LIMIT 2?

2 Answers2