MySQL Select minimum/maximum among two (or more) given values
MysqlSqlMysql Problem Overview
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?
Mysql Solutions
Solution 1 - Mysql
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
Both are described here http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html
Solution 2 - Mysql
Solution 3 - Mysql
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)
Solution 4 - Mysql
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;