MySQL query selecting the least non-null, non-zero integer
mysql
Solution
SELECT uniqueid,
FruitStand,
LEAST(
COALESCE(int1, int2, int3),
COALESCE(int2, int3, int1),
COALESCE(int3, int1, int2)) AS number
FROM myTable
For readability, I omitted the `NULLIF(intX, 0)` for each argument in `COALESCE`. You will need to add those to ignore zero values.
You will need to use `COALESCE` to avoid `NULL` values when using the `LEAST` function because as of MySQL 5.0.13, `LEAST` returns `NULL` if any argument is `NULL`.
See this question for details on that.
Problem
I have a table that has 5 columns: `uniqueid`, `int1`, `int2`, `int3`, and `FruitStand`. What I'm looking for is an example on how to do a single select that outputs the data of whichever column has the least non-null, non-zero integer. Example Table: ``` uniqueid|int1|int2|int3|FruitStand 1 | 2| 3| 4|Apples 2 | 21| 4| 0|Oranges 3 |NULL| 2| 5|Pears ``` So I would get the results from this query of ``` 1 | 2|Apples 2 | 4|Oranges 3 | 2|Pears ```