Finding Least/greatest Values From Combined Columns, Ignore 0 & Null- Mysql

I've got a dataset with a bunch of rows for monthly salary payments for each account. we have 6 columns for this - Salary_1, Salary_2, Salary_3, Salary_4, Salary_5 and Salary_6. So

Solution 1:

Greatest and Least do not ignore nulls like aggregation functions do; you'll need to do something to avoid them. One option is something like this:

Greatest(IFNULL(Salary_1 ,0), ...)

Least(
     CASEWHEN Salary_1 ISNULLOR Salary_1 =0THEN/*some huge value*/ELSE Salary_1 END
   , CASEWHEN Salary_2
   ....)

Solution 2:

This might be simplest to unpivot and aggregate the data:

select id, max(salary), min(salary)
from ((select id, salary_1 as salary from t) union all
      (select id, salary_2 as salary from t) union all
      . . .
     ) t
group by id;

This is definitely more expensive than a giant case expression. On the other hand, it is less prone to error.

The real suggestion is to fix your data model. Trying to store an array in multiple columns is generally a sign of a poor data model. The more appropriate method would have one row per salary rather than putting them in separate columns.