Cacher is the code snippet organizer for pro developers

DELETE FROM tableName
WHERE  ID NOT IN (SELECT MAX(ID)
                  FROM (SELECT * FROM tableName) AS somAlias
                  GROUP  BY colA,
                            colB,
                            colC
                  /*Even if ID is not null-able SQL Server treats MAX(ID) as potentially
                    nullable. Because of semantics of NOT IN (NULL) including the clause
                    below can simplify the plan*/
                  HAVING MAX(ID) IS NOT NULL)