How consider NULL as the MAX date instead of ignoring it in MySQL?

Question

Lets say that I have a table named test like this:

ID   DATE     

1     '2013-01-26'
1     NULL
1     '2013-03-03'      
2     '2013-02-23'      
2     '2013-04-12'      
2     '2013-05-02'   

And I would like to get from this table :

ID   DATE     

1     NULL    
2     '2013-05-02'   

Here is my query:

select ID, max(DATE)
from test
group by ID

Problem is that MYSQL ignores NULL values and returns me

ID   DATE     

1     '2013-03-03'   
2     '2013-05-02' 

How can i do so when there is a NULL it takes the MAX as NULL?

Solution 1

Give this a shot:

SELECT ID, case when MAX(DATE IS NULL) = 0 THEN max(DATE) END AS DATE
FROM test
GROUP BY ID;

Solution 2

Null is not a value or a number it's just null. This is why you use "where col1 is null" and not "col1 = null". The workaround is to use IFNULL and set a really high value.

select ID, max(IFNULL(DATE,'3000-01-01'))
from test
group by ID

Solution 3

FYI NULL is ignored when used in aggregation function.

mysql> SELECT * FROM null_test;
+------+
| c    |
+------+
| NULL |
|    1 |
|    2 |
|    3 |
+------+
4 rows in set (0.00 sec)

mysql> SELECT COUNT(c) FROM null_test;
+----------+
| COUNT(c) |
+----------+
|        3 | <= not 4 but 3
+----------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) FROM null_test;
+----------+
| COUNT(*) |
+----------+
|        4 | <= in this case not 3 but 4
+----------+
1 row in set (0.00 sec)

mysql> SELECT AVG(c) FROM null_test;
+--------+
| AVG(c) |
+--------+
| 2.0000 | <= not (1+2+3) / 4 but (1+2+3) / 3
+--------+
1 row in set (0.00 sec)

Solution 4

You could use an IF statement:

SELECT ID, IF(max(DATE IS NULL) = 0, max(DATE), NULL) AS DATE
FROM test
GROUP BY ID;

View more solutions

Comments

[user207421]

Any conforming SQL implementation should do that.

[user2741700]

It works thanks. Can you just please explain me MAX(DATE IS NULL) = 0

[Samuel O'Malley]

Basically saying if there are no NULLs then use max(DATE)

[Samuel O'Malley]

You could be more verbose and put THEN max(DATE) ELSE NULL

[ezdazuzena]

you are indirectly introducing a maximum date and not using NULL

[HoldOffHunger]

IFNULL() is much cleaner syntax.