select the row with duplicated value from oracle table

oracle table with column name, but there are some duplicated name value inside the table, how to select those rows ? how to delete these rows, for example if 2 rows with name=Tom, I need delete 1 of 2.

thanks
keepworkingAsked:
Who is Participating?
 
gatorvipCommented:
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:4301254394074

1. Select

select * from t
  where rowid in ( select rid
                     from ( select rowid rid,
                                   row_number() over
                                          ( partition by x order by rowid ) rn
                              from t
                           )
                    where rn > 1 )

2. Delete

delete from t
  where rowid in ( select rid
                     from ( select rowid rid,
                                   row_number() over
                                          ( partition by x order by rowid ) rn
                              from t
                           )
                    where rn > 1 )
0
 
cklautauCommented:
If all the columns are identical and you just want to get rid of 1 of the 2 rows, execute:
delete from table where name = 'Tom' and rownum=1;

That's it !
0
 
OP_ZaharinCommented:
- delete duplicate  in 1 statement:
delete from tblname where rowid not in (select max(rowid) from tblname group by columnA, columnB);

Open in new window



- you can also use any of the 4 methods on deleting duplicate records as shown in the following link:
http://www.orafaq.com/wiki/SQL_FAQ
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.