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?
解决方案
Give this a shot:
SELECT ID, case when MAX(DATE IS NULL) = 0 THEN max(DATE) END AS DATE
FROM test
GROUP BY ID;