sql delete duplicate rows but keep one

1DELETE FROM dups a USING (
2      SELECT MIN(ctid) as ctid, key
3        FROM dups 
4        GROUP BY key HAVING COUNT(*) > 1
5      ) b
6      WHERE a.key = b.key 
7      AND a.ctid <> b.ctid

1WITH cte AS (
2    SELECT 
3        contact_id, 
4        first_name, 
5        last_name, 
6        email, 
7        ROW_NUMBER() OVER (
8            PARTITION BY 
9                first_name, 
10                last_name, 
11                email
12            ORDER BY 
13                first_name, 
14                last_name, 
15                email
16        ) row_num
17     FROM 
18        sales.contacts
19)
20DELETE FROM cte
21WHERE row_num > 1;
22

1-- Oracle
2DELETE films
3WHERE rowid NOT IN (
4    SELECT min(rowid)
5    FROM films
6    GROUP BY title, uk_release_date
7);

1# Step 1: Copy distinct values to temporary table
2CREATE TEMPORARY TABLE tmp_user (
3    SELECT id, name 
4    FROM user
5    GROUP BY name
6);
7
8# Step 2: Remove all rows from original table
9DELETE FROM user;
10
11# Step 3: Remove all rows from original table
12INSERT INTO user (SELECT * FROM tmp_user);
13
14# Step 4: Remove temporary table
15DROP TABLE tmp_user;
16

1--ID should be primary key
2
3--get duplicate records using RANK
4SELECT E.ID, 
5    E.firstname, 
6    E.lastname, 
7    E.country, 
8    T.rank
9FROM [SampleDB].[dbo].[Employee] E
10  INNER JOIN
11(
12 SELECT *, 
13        RANK() OVER(PARTITION BY firstname, 
14                                 lastname, 
15                                 country
16        ORDER BY id) rank
17 FROM [SampleDB].[dbo].[Employee]
18) T ON E.ID = t.ID;
19
20--delete duplications
21DELETE E
22    FROM [SampleDB].[dbo].[Employee] E
23         INNER JOIN
24    (
25        SELECT *, 
26               RANK() OVER(PARTITION BY firstname, 
27                                        lastname, 
28                                        country
29               ORDER BY id) rank
30        FROM [SampleDB].[dbo].[Employee]
31    ) T ON E.ID = t.ID
32    WHERE rank > 1;

1Distinct: helps to remove all the duplicate
2records when retrieving the records from a table.
3
4SELECT DISTINCT FIRST_NAME FROM VISITORS;