MySql MAX function

MySql MAX function

MySql MAX function is used to find the particular column maximum value in the mysql table.

MySql MAX function syntax,

select MAX(column-name) from <table-name>;

Let us see the example to find highest salary in mysql table 'tblemployee1'.

mysql> select * from tblemployee1;
+--------+----------+---------+--------+
| emp_id | emp_name | dept_id | salary |
+--------+----------+---------+--------+
|      1 | emp1     |      10 |  10000 |
|      2 | emp2     |      11 |  20000 |
|      3 | emp3     |      10 |  15000 |
|      4 | emp4     |      11 |  12000 |
+--------+----------+---------+--------+
4 rows in set (0.00 sec)

mysql> select MAX(salary) from tblemployee1;
+-------------+
| MAX(salary) |
+-------------+
|       20000 |
+-------------+
1 row in set (0.38 sec)

Let us see the example to find second highest salary in mysql table 'tblemployee1'.

Using subquery,

mysql> select * from tblemployee1;
+--------+----------+---------+--------+
| emp_id | emp_name | dept_id | salary |
+--------+----------+---------+--------+
|      1 | emp1     |      10 |  10000 |
|      2 | emp2     |      11 |  20000 |
|      3 | emp3     |      10 |  15000 |
|      4 | emp4     |      11 |  12000 |
+--------+----------+---------+--------+
4 rows in set (0.00 sec)

mysql> select MAX(salary) from tblemployee1 
where salary not in (select MAX(salary) from tblemployee1);
+-------------+
| MAX(salary) |
+-------------+
|       15000 |
+-------------+
1 row in set (0.38 sec)

Using Not equals,

mysql> select MAX(salary) from tblemployee1 
  where salary <>
 (select MAX(salary) from tblemployee1);
+-------------+
| MAX(salary) |
+-------------+
|       15000 |
+-------------+
1 row in set (0.00 sec)

mysql>

« Previous Next »