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)
Published on 12-Dec-2019 07:40:43
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP