sql - What I am trying to do is, to eliminate all rows which are having modified date less than the maximum date for each entry in CI_NAME column

Question

DELETE FROM MOBILE_CELONIS_SITES 
WHERE CI_NAME IN (SELECT CI_NAME 
                  FROM MOBILE_CELONIS_SITES 
                  GROUP BY CI_NAME 
                  HAVING COUNT(CI_NAME) > 1) 
HAVING MODIFIED_DATE != (SELECT MAX(MODIFIED_DATE) 
                         FROM MOBILE_CELONIS_SITES 
                         GROUP BY CI_NAME);

I get an error:

Execution error: Incorrect syntax near the keyword 'HAVING'.

What I am trying to do is, to eliminate all rows which are having modified date less than the maximum date for each entry in CI_NAME column

question from:https://stackoverflow.com/questions/65889880/what-i-am-trying-to-do-is-to-eliminate-all-rows-which-are-having-modified-date

Answer 1

Use exists as follows:

DELETE FROM MOBILE_CELONIS_SITES t
WHERE exists 
      (Select 1 from MOBILE_CELONIS_SITES tt
        Where t.CI_NAME = tt.CI_NAME
          And tt.MODIFIED_DATE > t.MODIFIED_DATE)