Reputation:
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
Upvotes: 3
Views: 5169
Reputation: 6189
SELECT LEAST(
IF(int1,int1,999999),
IF(int2,int2,999999),
IF(int3,int3,999999)
);
Here, the value 999999 should be the maximum possible value for those columns.
Upvotes: 4
Reputation: 55334
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.
Upvotes: 7