title: Select max date or null date from same table MySQL
body: <p>I would to select the max date or null but display twice for the Company Code.  </p>
  
  <p>The original table is </p>
  
  <pre><code>Employee code      Company Code     resignation date
  001                     A               1/2/2013
  001                     B               30/12/2014
  002                     B               10/10/2000
  003                     A               1/12/2014
  003                     c               NULL
  </code></pre>
  
  <p>should be show</p>
  
  <pre><code>EMPLOYEE_CODE      COMPANY_CODE     RESIGNATION_DATE
  001                     B                30/12/2014
  002                     B               10/10/2000
  003                     C                NULL
  </code></pre>
  
  <p>but I only want to show one record per EMPLOYEE_CODE</p>
  
  <pre><code>EMPLOYEE_CODE      COMPANY_CODE     RESIGNATION_DATE
  001                     A               1/2/2013
  001                     B               30/12/2014
  002                     B               10/10/2000
  003                     C 
  </code></pre>
  
  <p>this is my query</p>
  
  <pre><code>SELECT EMPLOYEE_CODE , COMPANY_CODE, RESIGNATION_DATE
       FROM MT_EMPLOYEE_CONTRACT WHERE RESIGNATION_DATE IS NULL
  UNION
  SELECT EMPLOYEE_CODE , COMPANY_CODE, MAX(RESIGNATION_DATE) AS RESIGNATION_DATE
       FROM MT_EMPLOYEE_CONTRACT WHERE WHERE EMPLOYEE_CODE NOT IN  
       (SELECT EMPLOYEE_CODE FROM MT_EMPLOYEE_CONTRACT WHERE RESIGNATION_DATE IS NULL)   
       GROUP BY EMPLOYEE_CODE;
  </code></pre>
  
  <p>Can Anyone find out what is the problem?
  Thank you</p>