MySQL select max date or null date

mysql, sql

Solution

Can Anyone find out what is the problem?

In this condition :

MAX(RESIGNATION_DATE IS NULL)= 0

you are comparing `MAX(NULL)=0` as `RESIGNATION_DATE IS NULL`

Your query 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

Problem

I would to select the max date or null but it shows a error message. Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=IS;, CASE WHEN MAX(DATE;CONCAT, DRIVER=3.64.114 The original table is ``` Employee code resignation date 001 1/2/2013 001 1/5/2014 001 null 002 10/10/2000 ``` should be show ``` EMPLOYEE_CODE RESIGNATION_DATE 001 null 002 10/10/2000 ``` this is my query ``` 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 is the problem? Thank you

Original source