Select the maximum for each value in a MySQL table?
For this, use GROUP BY clause along with MAX(). Let us first create a table −
mysql> create table DemoTable
-> (
-> CountryName varchar(20),
-> Population int
-> );
Query OK, 0 rows affected (0.56 sec)
Insert some records in the table using insert command −
mysql> insert into DemoTable values('US',560);
Query OK, 1 row affected (0.23 sec)
mysql> insert into DemoTable values('UK',10090);
Query OK, 1 row affected (0.15 sec)
mysql> insert into DemoTable values('UK',8794);
Query OK, 1 row affected (0.19 sec)
mysql> insert into DemoTable values('US',1090);
Query OK, 1 row affected (0.21 sec)Display all records from the table using select statement −
mysql> select *from DemoTable;
This will produce the following output −
+-------------+------------+
| CountryName | Population |
+-------------+------------+
| US | 560 |
| UK | 10090 |
| UK | 8794 |
| US | 1090 |
+-------------+------------+
4 rows in set (0.00 sec)
Following is the query to select the maximum for each value in a MySQL table −
mysql> select CountryName,max(Population) from DemoTable group by CountryName;
This will produce the following output −
+-------------+-----------------+
| CountryName | max(Population) |
+-------------+-----------------+
| US | 1090 |
| UK | 10090 |
+-------------+-----------------+
2 rows in set (0.00 sec)
Published on 18-Dec-2019 05:03:19
