Query to Remove the Duplicates from a Data Set

Assume I have a table TMP with structure as given below.

view plaincopy to clipboardprint?

1. CREATE TABLE TMP (
2. NAME VARCHARW(30)
3. )

I have populated the duplicate values as well.

view plaincopy to clipboardprint?

1. SELECT * FROM TEMP;
2. 
   
3. Output
4. ------
5. Rahim
6. Vicky
7. Sai
8. Tom
9. Rahim
10. Rahim
11. Vicky
12. Sai
13. Jaz
14. Apple
15. Apple
16. Rahim

Query to remove the Duplicate.

Method 1.

view plaincopy to clipboardprint?

1. WITH ds AS ( SELECT rownum AS ID, name FROM tmp),  
2. dup_set AS (SELECT id, RANK() OVER(PARTITION    
3. BY name ORDER BY ROWNUM) AS rk, name from ds)  
4. SELECT name FROM dup_set WHERE rk=1;  

Output:

1. Rahim
2. Vicky
3. Sai
4. Tom
5. Jaz
6. Apple

Method 2.

1. WITH set_a AS  
2.   ( SELECT ROWNUM AS id, name FROM tmp a  
3.   ),  
4.   set_b AS  
5.   (SELECT ROWNUM AS id, name FROM tmp b  
6.   )  
7. SELECT a.name  
8. FROM set_a a  
9. WHERE a.id >= ALL  
10.   (SELECT id FROM set_b b   
11.   WHERE a.name=b.name  
12.   );  

Method 3.

1. WITH rem_dup_set AS (
2. SELECT ROW_NUMBER() OVER(PARTITION BY name
3.    ORDER BY name) AS rk, name
4. FROM temp )
5. SELECT name FROM rem_dup_set WHERE rk=1;

Output:

1. Rahim
2. Vicky
3. Sai
4. Tom
5. Jaz
6. Apple