Using GROUP BY and MAX on multiple columns in MySQL?
To understand the GROUP BY and MAX on multiple columns, let us first create a table. The query to create a table is as follows −
mysql> create table GroupByMaxDemo
-> (
-> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> CategoryId int,
-> Value1 int,
-> Value2 int
-> );
Query OK, 0 rows affected (0.68 sec)
Example
Insert some records in the table using insert command. The query is as follows −
mysql> insert into GroupByMaxDemo(CategoryId, Value1,Value2) values(10,100,50);
Query OK, 1 row affected (0.15 sec)
mysql> insert into GroupByMaxDemo(CategoryId, Value1,Value2) values(10,100,70);
Query OK, 1 row affected (0.21 sec)
mysql> insert into GroupByMaxDemo(CategoryId, Value1,Value2) values(10,50,100);
Query OK, 1 row affected (0.22 sec)
mysql> insert into GroupByMaxDemo(CategoryId, Value1,Value2) values(20,180,150);
Query OK, 1 row affected (0.19 sec)
Display all records from the table using a select statement. The query is as follows −
mysql> select *from GroupByMaxDemo;
Output
+----+------------+--------+--------+
| Id | CategoryId | Value1 | Value2 |
+----+------------+--------+--------+
| 1 | 10 | 100 | 50 |
| 2 | 10 | 100 | 70 |
| 3 | 10 | 50 | 100 |
| 4 | 20 | 180 | 150 |
+----+------------+--------+--------+
4 rows in set (0.00 sec)
Example
The following is the query to use GROUP BY and MAX on multiple columns −
mysql> select tbl2.CategoryId, tbl2.Value1, max(tbl2.Value2)
-> from
-> (
-> select CategoryId, max(Value1) as `Value1`
-> from GroupByMaxDemo
-> group by CategoryId
-> ) tbl1
-> inner join GroupByMaxDemo tbl2 on tbl2.CategoryId = tbl1.CategoryId and tbl2.Value1 = tbl1.Value1
-> group by tbl2.CategoryId, tbl2.Value1;
Output
+------------+--------+------------------+
| CategoryId | Value1 | max(tbl2.Value2) |
+------------+--------+------------------+
| 10 | 100 | 70 |
| 20 | 180 | 150 |
+------------+--------+------------------+
2 rows in set (0.00 sec)
Published on 19-Mar-2019 12:21:01
