CODEWITHSUNDEEP
mysql
Charan Singh
Charan Singh

Reputation: 33

Comparing Multiple Columns with and without NULL values

5, Not sure what am I doing wrong, please help. As it seems in a query on multiple column comparison to find least value, NULL continues to show up as a Resultant instead of least value 

SELECT
  IF(col1 IS NULL OR col2 IS NULL OR col3 IS NULL OR col4 IS NULL OR col5 IS NULL, 
      COALESCE(col1,col2,col3,col4,col5), 
      LEAST(col1,col2,col3,col4,col5)
  ) As Resultant 
from db.tablename 
Group by Id;

Alternatively tried CASE select without much success. Thanks

Upvotes: 3

Views: 1875

Answers (2)

Stephen
Stephen

Reputation: 3432

For people with the case such that there are only two values to compare, you can do this:

GREATEST(ifnull(a,b), ifnull(b,a))

Upvotes: 0

mdprotacio
mdprotacio

Reputation: 842

if null is considered 0

select least(ifnull(col1,0),ifnull(col2,0),ifnull(col3,0),ifnull(col4,0),ifnull(col5,0))
as Resultant
from db.tablename
Group by Id;

if null is condered max

select least(ifnull(col1,~0>>1),ifnull(col2,~0>>1),ifnull(col3,~0>>1),ifnull(col4,~0>>1),ifnull(col5,~0>>1))
as Resultant
from db.tablename
Group by Id;

Upvotes: 1

Related Questions