Find “greatest” between two columns and display with some records already null in MySql

Let us first create a table −

mysql> create table DemoTable
   -> (
   -> Value1 int,
   -> Value2 int
   -> );
Query OK, 0 rows affected (0.77 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable values(78,89);
Query OK, 1 row affected (0.17 sec)
mysql> insert into DemoTable values(19,null);
Query OK, 1 row affected (0.23 sec)
mysql> insert into DemoTable values(null,0);
Query OK, 1 row affected (0.21 sec)
mysql> insert into DemoTable values(null,95);
Query OK, 1 row affected (0.15 sec)

Display all records from the table using select statement −

mysql> select *from DemoTable;

This will produce the following output −

+--------+--------+
| Value1 | Value2 |
+--------+--------+
|     78 |     89 |
|     19 |   NULL |
|   NULL |      0 |
|   NULL |     95 |
+--------+--------+
4 rows in set (0.00 sec)

Here is the query to find the “greatest” from two columns −

mysql> select greatest(if(Value1 is null,0,Value1),if(Value2 is null,0,Value2)) from DemoTable;

This will produce the following output −

+-------------------------------------------------------------------+
| greatest(if(Value1 is null,0,Value1),if(Value2 is null,0,Value2)) |
+-------------------------------------------------------------------+
|                                                                89 |
|                                                                19 |
|                                                                 0 |
|                                                                95 |
+-------------------------------------------------------------------+
4 rows in set (0.00 sec)

AmitDiwan

Updated on 12-Dec-2019 07:41:07

• Related Questions & Answers
• Display records ignoring NULL in MySQL
• Display records from two columns based on comparison in MySQL?
• Display only NOT NULL values from a column with NULL and NOT NULL records in MySQL
• Display and concatenate records ignoring NULL values in MySQL
• Find and display duplicate records in MySQL?
• How to find records with a null value in a set of columns with MySQL
• Display NULL and NOT NULL records except a single specific value in MySQL
• Display records with more than two occurrences in MySQL?
• Count only null values in two different columns and display in one MySQL select statement?
• Exclude some ID records from a list and display rest in MySQL
• How to display some columns (not all) in MySQL?
• How to get the greatest of two columns values in MySQL?
• SELECT not null column from two columns in MySQL?
• Selecting records within a range and with a condition set on two columns in MySQL?
• Divide numbers from two columns and display result in a new column with MySQL