Biggest value from two or more fields in MySQL?

MySQLMySQLi Database

To know the biggest value from two or more fields, use the function GREATEST() from MySQL.

The syntax is as follows −

SELECT GREATEST(MAX(yourColumnName1),MAX(yourColumnName2),...............MAX(yourColumnName2) ) from yourTableName;

Let us understand the above concept by creating a table with more than two columns −

mysql> create table GreatestOfTwoOrMore
   -> (
   -> Marks1 int,
   -> Marks2 int,
   -> Marks3 int
   -> );
Query OK, 0 rows affected (0.57 sec)

Here is the query to insert records in a table −

mysql> insert into GreatestOfTwoOrMore values(23,78,89);
Query OK, 1 row affected (0.16 sec)
mysql> insert into GreatestOfTwoOrMore values(50,100,150);
Query OK, 1 row affected (0.24 sec)
mysql> insert into GreatestOfTwoOrMore values(100,500,2000);
Query OK, 1 row affected (0.15 sec)

Display all the values inserted above using the following query −

mysql> select *from GreatestOfTwoOrMore;

The following is the output −

+--------+--------+--------+
| Marks1 | Marks2 | Marks3 |
+--------+--------+--------+
|     23 |     78 |     89 |
|     50 |    100 |    150 |
|    100 |    500 |   2000 |
+--------+--------+--------+
3 rows in set (0.00 sec)

Let us implement the above concept using the following query to get the biggest value from two or more fields.

The query is as follows −

mysql> SELECT GREATEST(MAX(marks1),MAX(marks2),MAX(marks3)) from GreatestOfTwoOrMore;

The following is the output −

+-----------------------------------------------+
| GREATEST(MAX(marks1),MAX(marks2),MAX(marks3)) |
+-----------------------------------------------+
|                                          2000 |
+-----------------------------------------------+
1 row in set (0.06 sec)
raja
Published on 24-Dec-2018 06:29:29
Advertisements