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;