title: Delete invalid row(duplicate) from table which has the valid entry
body: <p>I need delete the duplicate entries which has the status of inactive in <code>empl_status</code> value . For each employee there should only one active <code>emp_id</code> other rows need to be deleted.</p>
  
  <pre><code>DELETE
  FROM EMPLOYEE_MAT
  WHERE EMP_ID IN
    (SELECT MAX(EM.EMP_ID),
      (SELECT ASSOCIATE_ID FROM EMPLOYEE WHERE USER_ID =UT.EMP_GUID
      ) AS user_name,
      EM.EMP_STATUS
    FROM EMPLOYEE_MAT EM
    JOIN EMP_TRANS_TABLE ET
    ON EM.UT_ID = ET.UT_ID
    WHERE EM.EMP_STATUS NOT LIKE 'Active'
    GROUP BY EM.EMP_ID ,
      ET.EMP_GUID,
      EM.EMP_STATUS
    HAVING COUNT(1) &gt; 1
    ORDER BY EM.EMP_ID,
      user_name
    )
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/9ZdiI.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/9ZdiI.png" alt="enter image description here"></a></p>