How to get the greatest of two columns values in MySQL?

In order to get the greatest of two columns values in MySQL, you need to use GREATEST() function. Following is the syntax:

select greatest(yourColumnName1,yourColumnName2) AS anyAliasName from yourTableName; 

Let us first create a table:

mysql> create table DemoTable
(
   Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   Number1 int,
   Number2 int
);
Query OK, 0 rows affected (0.63 sec)

Following is the query to insert some records in the table using insert command:

mysql> insert into DemoTable(Number1,Number2) values(1000,10000);
Query OK, 1 row affected (0.49 sec)
mysql> insert into DemoTable(Number1,Number2) values(600,900);
Query OK, 1 row affected (0.12 sec)
mysql> insert into DemoTable(Number1,Number2) values(12,9);
Query OK, 1 row affected (0.33 sec)
mysql> insert into DemoTable(Number1,Number2) values(19,56);
Query OK, 1 row affected (0.17 sec)

Following is the query to display records from the table using select command:

mysql> select *from DemoTable;

This will produce the following output:

+----+---------+---------+
| Id | Number1 | Number2 |
+----+---------+---------+
|  1 | 1000    | 10000   |
|  2 | 600     | 900     |
|  3 | 12      | 9       |
|  4 | 19      | 56      |
+----+---------+---------+
4 rows in set (0.00 sec)

Following is the query to get the greatest of two column values in MySQL:

mysql> select greatest(Number1,Number2) AS MAXIMUM_NUMBER_OF_TWO_COLUMNS from DemoTable;

This will produce the following output:

+-------------------------------+
| MAXIMUM_NUMBER_OF_TWO_COLUMNS |
+-------------------------------+
| 10000                         |
| 900                           |
| 12                            |
| 56                            |
+-------------------------------+
4 rows in set (0.00 sec)

Above, you can see out of 1000 and 10000, the value displayed is 10000 i.e. the greatest value. In the saw way it works for others i.e. 900 out of 600 and 900.

Updated on: 30-Jul-2019

145 Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements