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

MySQLMySQLi Database

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.

raja
Published on 29-Apr-2019 10:56:11
Advertisements