MAX: Get largest value of column

MAX(column) returns the largest value of the selected column.

I am going to use following sample data.

mysql> SELECT * FROM employee;
+----+---------+------+-------------+---------------------+------------+
| id | name    | age  | salary      | mailId              | city       |
+----+---------+------+-------------+---------------------+------------+
|  1 | Hari    |   28 |  12345.6700 | hari@hari.com       | Bangalore  |
|  2 | Sandesh |   31 |  98345.0000 | sandesh@sandesh.com | Trivendram |
|  3 | Phalgum |   33 | 119345.6700 | phalgun@hari.com    | Hyderabad  |
|  4 | Manju   |   36 |  87666.8700 | manju@sandesh.com   | Bangalore  |
|  5 | Rakesh  |   26 |  38000.0000 | rakesh@hari.com     | Bangalore  |
|  6 | Sankalp |   38 |  87645.6700 | sankalp@sankalp.com | Chenai     |
|  7 | Vadiraj |   40 |  12345.6700 | vadi@hari.com       | Bangalore  |
|  8 | Prasob  |   37 |  12345.6700 | prasob@sandesh.com  | Trivendram |
|  9 | Kesav   | NULL | 123457.8900 | NULL                | NULL       |
+----+---------+------+-------------+---------------------+------------+
9 rows in set (0.00 sec)

Get maximum salary of the employee.

mysql> SELECT MAX(salary) FROM employee;
+-------------+
| MAX(salary) |
+-------------+
| 123457.8900 | 
+-------------+
1 row in set (0.00 sec)

Get employee details who is getting maximum salary.

mysql> SELECT * FROM employee WHERE salary = (SELECT MAX(salary) FROM employee);
+----+-------+------+-------------+--------+------+
| id | name  | age  | salary      | mailId | city |
+----+-------+------+-------------+--------+------+
|  9 | Kesav | NULL | 123457.8900 | NULL   | NULL |
+----+-------+------+-------------+--------+------+
1 row in set (0.00 sec)

Get employee maximum salary city wise

mysql> SELECT city, MAX(salary) FROM employee GROUP BY city;
+------------+-------------+
| city       | MAX(salary) |
+------------+-------------+
| NULL       | 123457.8900 |
| Bangalore  |  87666.8700 |
| Chenai     |  87645.6700 |
| Hyderabad  | 119345.6700 |
| Trivendram |  98345.0000 |
+------------+-------------+
5 rows in set (0.00 sec)

You can escape NULL values by adding IS NOT NULL clause.

mysql> SELECT city, MAX(salary) FROM employee WHERE city IS NOT NULL GROUP BY city; 
+------------+-------------+
| city       | MAX(salary) |
+------------+-------------+
| Bangalore  |  87666.8700 |
| Chenai     |  87645.6700 |
| Hyderabad  | 119345.6700 |
| Trivendram |  98345.0000 |
+------------+-------------+
4 rows in set (0.00 sec)

Previous                                                 Next                                                 Home