Comparing Multiple Columns with and without NULL values

Question

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

What is the purpose of the GROUP BY? You have no aggregates in there. — Michael Berkowski
– Michael Berkowski, Commented Jan 31, 2012 at 17:27
@a1ex07 Scenario is , there are not null/null values in all columns. If col1, col2 are null in a row, there are col3, col4, col5 with values/ not null in the same row. I tried assigning value to Null as '999' [ Select *, MIN(IFNULL(Col1, '999'), MIN(IFNULL(Col2, '999')...] but that didnt seem to work either. — Charan Singh
– Charan Singh, Commented Jan 31, 2012 at 17:40

mdprotacio · Accepted Answer · 2012-01-31 17:39:59Z

1

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;

answered Jan 31, 2012 at 17:39

mdprotacio

8426 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

17 Comments

Charan Singh Over a year ago

Thanks @ Melvin, ran both queries Resultant for a Col1 (null), Col2(null), Col3(not null), Col4(not null), Col5(not null) Brings Resultant (null) for that row, only when all Col1 to Col5 are not null the resultant fetches not null and that is also the least, correct, value amongst all columns.

mdprotacio Over a year ago

are you saying that the ifnull(Col1,0) does not result to 0 when Col1 is null?

Charan Singh Over a year ago

Instead of ifnull( col1,0) I changed to ifnull(col1,999) with '999'as the value for null, so that I can have non null/least value of the five columns as resultant values. But the resultant bring null instead of least value that exists in five columns when compared. It also brings least value of the five when there is no null value substituted.

mdprotacio Over a year ago

ifnull(col1,0) is if we are going to treat nulls as 0 value, while ifnull(col1,~0>>1) is if we are going to treat nulls as max int value remember that you may reach the point where 999 is also a result of your query or could be higher making nulls interpreted as 999 to be the least.

Charan Singh Over a year ago

Thanks again,Mevin, though I would appreciate your help in resolving this: there are not null/null values in all columns from Col1 to Col5. If col1, col2 are null in a row, there are col3 (160), col4 (170), col5(170) with values/ not null in the same row. I want to ignore Null Values and select the least from the non null values ( which would in this case be (160), Am I following the correct query to find the resultant value that is lowest /least when compared in Five columns with and without nulls? OR IF you know any alternative to GREATEST_NONNULL(...); or Least_NONNULL(col1, col2...) ?

|

Stephen · Accepted Answer · 2015-07-09 16:23:42Z

0

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))

answered Jul 9, 2015 at 16:23

Stephen

3,4421 gold badge24 silver badges22 bronze badges

Collectives™ on Stack Overflow

Comparing Multiple Columns with and without NULL values

2 Answers 2

17 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

17 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related