Delete duplicate rows from table , we are considering duplicate rows based on Name and City column Values.
--Works well if Table Desn't have primary key
WITH TEMP AS (
SELECT ROW_NUMBER() OVER ( PARTITION BY NAME,CITY ORDER BY NAME,CITY) as RN,NAME,CITY
FROM TAbleDuplicate
)
DELETE FROM TEMP WHERE
RN>1
Another way to delete duplicate from table (if primary key is present in table):
DELETE FROM EMPLOYEE WHERE EMP_ID NOT IN (
SELECT Min(EMP_ID) FROM EMPLOYEE
GROUP BY FIRST_NAME,LAST_NAME,SALARY )
