Deleting duplicates rows from oracle

问题:

I am using oracle database.I want to use duplicate rows from a table except one,which means that I want to delete all rows but atleast one row should be there. I have a table

employee_id ---- department_id
1                     10
2                     10
1                     20
3                     30
2                     30

Now i want to delete duplicate rows but at least one row should be there.

select count(employee_id),employee_id 
from employee
group by employee_id
having count(employee_id) >1));

i had used this to find number of employees that are in more than one department but could not find a way to move further. If i use a delete there it will delete all duplicates,But i want to keep one copy.

delete from empl where eno IN( select eno from( select count(eno),eno from empl group by eno having count(eno) >1));

EDIT:I want to retain employee_id Any one who can guide me further

回答1:

delete from employee a
where employee_id in (
   select employee_id 
   from employee b
   where b.department_id > a.department_id )

回答2:

Use subquery to delete duplicate rows

Here we see an example of using SQL to delete duplicate table rows using an SQL subquery to identify duplicate rows, manually specifying the join columns:

DELETE FROM
   table_name A
WHERE
  a.rowid >
   ANY (
     SELECT
        B.rowid
     FROM
        table_name B
     WHERE
        A.col1 = B.col1
     AND
        A.col2 = B.col2
        );

Use RANK to delete duplicate rows

This is an example of the RANK function to identify and remove duplicate rows from Oracle tables, which deletes all duplicate rows while leaving the initial instance of the duplicate row:

delete from $table_name where rowid in
  (
  select "rowid" from
     (select "rowid", rank_n from
         (select rank() over (partition by $primary_key order by rowid) rank_n, rowid as "rowid"
             from $table_name
             where $primary_key in
                (select $primary_key from $table_name
                  group by $all_columns
                  having count(*) > 1
                )
             )
         )
     where rank_n > 1
  )

One of the most important features of Oracle is the ability to detect and remove duplicate rows from a table. While many Oracle DBA place primary key referential integrity constraints on a table, duplicate file finder many shops do not use RI because they need the flexibility.

回答3:

DELETE FROM names a WHERE ROWID > (SELECT MIN(ROWID) FROM names b WHERE b.name=a.name AND b.age=a.age