How to get the minimum of 3 values where some of them may be null in MySQL using LEAST()?

Question

I found this question ( How to get the max of two values in MySQL? ) while looking for ways to compare discrete set of values.

I want to be able to get the minimum of few selected values where some of them may be null because they are optional but the MySQL docs says:

If any argument is NULL, the result is NULL.

So why is your question how to do it using `Least` specifically? — Martin Smith, Oct 12 '11 at 18:46
Added LEAST in the title so that I don't confuse anyone this is MAX/MIN question. Any other working methodology is appriciated. — antitoxic, Oct 12 '11 at 18:48

score 5 · Accepted Answer · answered Oct 12 '11 at 18:47

5

Use a COALESCE() function on the nullable values:

select LEAST(COALESCE(@foo, <max int>), COALESCE(@bar, <max int>));

if you get the max int value, then both were null (unless there's a decent chance you could actually have the max int value as a real argument, then more logic is necessary)

answered Oct 12 '11 at 18:47

Jeremy Holovacs

19,993
29
99
234

@MarkByers - true... I can't wrap my brain around a case where I would use LEAST() on character data, though. – Jeremy Holovacs Oct 12 '11 at 19:05
@Jeremy: to order words lexicographically - find the first one in a lexikon. – ypercubeᵀᴹ Oct 12 '11 at 19:07
@ypercube that seems a textbook case for order by/ limit to me... using `LEAST()`... well, I suppose you could do it. I can honestly say I've never used it, though, and have not felt its lack. – Jeremy Holovacs Oct 12 '11 at 19:11
I don't know about the char "max" but I guess it depends on the data. This solution (with the max int) works even better if the discrete set has a known maximum which is way lower then the max int. – antitoxic Oct 12 '11 at 20:05

Mark Byers · Answer 2 · 2011-10-12T18:54:22.753

2

How about this:

LEAST(COALESCE(col1, col2, col3),
      COALESCE(col2, col3, col1),
      COALESCE(col3, col1, col2))

Obviously this doesn't scale well to more than 3 values.

edited Oct 12 '11 at 18:54

answered Oct 12 '11 at 18:48

Mark Byers

719,658
164
1,497
1,412

it's an interesting approach, although I don't know how practical it is. Why reference the other columns like that? – Jeremy Holovacs Oct 12 '11 at 19:00
Oh I cannot argue that it will work, I just envision a case where you are running LEAST() against three of the exact same number because only one value wasn't null. Seems like a waste to me, but it probably makes no difference one way or another. – Jeremy Holovacs Oct 12 '11 at 19:07
It seems like a waste to me as well. The max number seems more light - Less comparisons to do. – antitoxic Oct 12 '11 at 19:57

score 1 · Answer 3 · answered Oct 12 '11 at 19:12

Works, is easily extendible, and doesn't rely on any values not being found in the data but probably heinously inefficient!

CREATE TABLE X
(
Id int primary key,
col1 int null,
col2 int null,
col3 int null
)

Query

SELECT id,
       Min(CASE c
             WHEN 1 THEN col1
             WHEN 2 THEN col2
             WHEN 3 THEN col3
           END)
FROM   x,
       (SELECT 1 AS c
        UNION ALL
        SELECT 2
        UNION ALL
        SELECT 3) t
GROUP  BY id

score 0 · Answer 4 · answered Oct 12 '11 at 19:17

You have to hard code a big number say 99999.

LEAST( IFNULL(COL_1,999999) ,
IFNULL(COL_2,999999) , IFNULL(COL_3,999999) , IFNULL(COL_1,999999) )

i.e. just add IFNULL then the value or column name with a big enough integer.

How to get the minimum of 3 values where some of them may be null in MySQL using LEAST()?

4 Answers4