user222427
user222427

Reputation:

MySQL query selecting the least non-null, non-zero integer

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

Answers (2)

Reza Mamun
Reza Mamun

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

Evan Mulawski
Evan Mulawski

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

Related Questions