Easy way to remove duplicates from a table

Here is an easy way to de-dupe a table. Union it against itself.  

Example:  

dmart> create table j (id number(10),name varchar2(10));

Table created.

dmart> desc j                
 Name                Null?    Type
 ------------------- -------- ---------------
 ID                           NUMBER(10) 
 NAME                         VARCHAR2(10)

dmart> insert into j values (5,'jay');

1 row created.

dmart> /

1 row created.

dmart> /

1 row created.

dmart> /

1 row created.

dmart> insert into j values (6,'jay');

1 row created.

dmart> insert into j values (5,'steve');

1 row created.

dmart> select * from j;

        ID NAME
---------- ----------

         5 jay
         5 jay
         5 jay
         5 jay
         6 jay
         5 steve

6 rows selected.

dmart> select * from j union select * from j;

        ID NAME
---------- ----------

         5 jay
         5 steve
         6 jay

dmart>  

When doing a union between two tables, this set function automatically removes duplicate rows (use UNION ALL if you do not want to remove dups) between the tables in question, but it does so AFTER sorting all the rows among them. So by UNION'ing a table to itself, it will take all rows from both tables, put them in order, then remove the duplicates. This eliminates all rows from the second table, but also any dups in the first, which is want you want. Received on Fri May 09 2003 - 16:17:36 CEST