0

In excel we can find the max. and min. value of a row by ignoring the blank column like this picture

enter image description here

In mysql database, I have a table with 3 fields, and data types of each field is float. By using the greatest and least function, I intend to find the max. and min. values for each row. But the result is

enter image description here

The results in excel and in mysql are different, because in excel we can empty the column, but in mysql (with float data type), the empty column will change to 0.

If the column contains NULL, the result is as follows

enter image description here

Question : Is there a way to make 0 or NULL value in mysql read as empty column?

mgae2m
  • 1,134
  • 1
  • 14
  • 41
Zhumpex
  • 121
  • 1
  • 3
  • 17
  • I'm not into mysql but someting like `SELECT GREATEST(A,B,C) AS MAX, LEAST(A,B,C) AS MIN FROM value WHERE (A,B,C)!=""` should work. – Mrig Sep 25 '17 at 10:14
  • https://stackoverflow.com/questions/9831851/mysql-get-max-or-greatest-of-several-columns-but-with-null-fields May this will help you – KMS Sep 25 '17 at 10:14

1 Answers1

1

Maybe you could use ifnull to substitute a low or a high value

drop table if exists t;
Create table t (a float, b float, c float);
insert into t (b,c) values (1,2);
select greatest(ifnull(a,0),ifnull(b,0),ifnull(c,0)) maxi,
         least(ifnull(a,999999),ifnull(b,999999),ifnull(c,999999)) mini
 from t;

Result

+------+------+
| maxi | mini |
+------+------+
|    2 |    1 |
+------+------+
1 row in set (0.00 sec)
P.Salmon
  • 17,104
  • 2
  • 12
  • 19