MySQL Select minimum/maximum among two (or more) given values

Question

Is it possible to SELECT the minimum or maximum among two or more values. I'd need something like this:

SELECT MAX_VALUE(A.date0, B.date0) AS date0, MIN_VALUE(A.date1, B.date1) AS date1
FROM A, B
WHERE B.x = A.x

Can I achieve this by only using MySQL?

possible duplicate of [How to get the max of two values in MySQL?](http://stackoverflow.com/questions/1565688/how-to-get-the-max-of-two-values-in-mysql) — RandomSeed, Oct 18 '13 at 09:18

Elon Than · Accepted Answer · 2016-09-06T12:49:09.600

309

You can use LEAST and GREATEST function to achieve it.

SELECT
    GREATEST(A.date0, B.date0) AS date0,
    LEAST(A.date1, B.date1) AS date1
FROM A, B
WHERE B.x = A.x

edited Sep 06 '16 at 12:49

answered Oct 18 '13 at 09:16

Elon Than

10

I'm shocked to have learned of LEAST and GREATEST only after 20 years in the business :-O – Oded Ben Dov Mar 02 '21 at 10:57
10 years here ! – Alcalyn Jul 10 '23 at 13:09

score 30 · Answer 2 · answered Oct 18 '13 at 09:17

30

I suppose you are looking for:

and

answered Oct 18 '13 at 09:17

RandomSeed

score 20 · Answer 3 · answered May 03 '18 at 22:23

20

Just watch out if NULL is likely to be in a field value ...

SELECT LEAST(NULL,NOW());

and

SELECT GREATEST(NULL,NOW());

both return null, which may not be what you want (especially in the case of GREATEST)

answered May 03 '18 at 22:23

Steve Childs

2

Which, for new comers, can be solved with IFNULL(, ) – Oded Ben Dov Mar 02 '21 at 10:56

score 3 · Answer 4 · answered Oct 18 '13 at 09:34

3

Try this:

SELECT GREATEST(A.date0, B.date0) AS `date0`,LEAST(A.date0, B.date0) AS `date1`
  FROM A 
  JOIN  B
    ON A.id = B.role;

answered Oct 18 '13 at 09:34

Mani

4 Answers4