MySQL selects maximum date or zero date

Question

I would choose the maximum date or zero, but it shows an error message.

Error: SQLCODE = -104, SQLSTATE = 42601, SQLERRMC = IS ;, CASE WHEN MAX (DATE; CONCAT, DRIVER = 3.64.114

Source table

Employee code           resignation date
001                     1/2/2013
001                     1/5/2014
001                     null
002                     10/10/2000

should be shown

EMPLOYEE_CODE           RESIGNATION_DATE
001                     null
002                     10/10/2000

this is my request

SELECT EMPLOYEE_CODE, 
       CASE 
           WHEN MAX(RESIGNATION_DATE IS NULL)= 0 
           THEN MAX(RESIGNATION_DATE) 
       END AS DATE
FROM MT_EMPLOYEE_CONTRACT
GROUP BY EMPLOYEE_CODE;

Can anyone find out what the problem is? Thanks you

Answer 1

Can anyone find out what the problem is?

In this condition:

MAX(RESIGNATION_DATE IS NULL)= 0

you compare MAX(NULL)=0howRESIGNATION_DATE IS NULL

Your request should be:

select emp_code, res_date /* first select null emp_code*/
From Table_name
where res_date IS NULL group by emp_code
UNION 
select emp_code, MAX(res_date ) /* select max from non - null emp_code*/
From Table_name
where emp_code NOT IN 
     (select emp_code From Table_name 
      where res_date IS NULL) 
group by emp_code

EDIT

select DISTINCT emp_code, res_date /* first select null emp_code*/
From Table_name
where res_date IS NULL 
UNION 
select emp_code, MAX(res_date ) /* select max from non - null emp_code*/
From Table_name
where emp_code NOT IN 
     (select emp_code From Table_name 
      where res_date IS NULL) 
group by emp_code 

Answer 2

try using a combination of NULLIF and IFNULL .

SELECT EMPLOYEE_CODE, NULLIF(MAX(IFNULL(RESIGNATION_DATE,0)),0)
FROM MT_EMPLOYEE_CONTRACT
GROUP BY EMPLOYEE_CODE;