Real Time Scenarios of ORACLE SQL Development - 3

Dealing with Duplicate records from a table using Row Number Function.

drop table student;

create table student(id int,name varchar2(10),dept int);

insert into student values(1,'Vishnu',111);

insert into student values(1,'Vishnu',111);

insert into student values(2,'Teja',222);

insert into student values(3,'Ashwin',222);

insert into student values(3,'Ashwin',222);

select * from student;

ROWNUM and ROWID keywords in ORACLE.

ROWNUM is a temporary numeric sequence number of a row.

ROWID is a physical address location of the row.

Assigning numeric number using ROW_NUMBER Function to each record in the table based on duplicates

Using rownum keyword.

select id,name,dept,rownum rnum,row_number() over (partition by id,name,dept order by id) as dup from student;

Using rowid keyword.

select id,name,dept,rowid rid,row_number() over (partition by id,name,dept order by id) as dup from student;

Extracting the rows with row num's which are duplicates

select rnum from  
(select id,name,dept,rownum rnum,row_number() over (partition by id,name,dept order by id) as dup from student) where dup>1;

Extracting the rows with rowid's which are duplicates

select rid from

(select id,name,dept,rowid rid,row_number() over (partition by id,name,dept order by id) as dup from student) where dup>1;

Finally deleting the duplicate records from the table student.

delete from student where rowid in 
(select rid from (select id,name,dept,rowid rid,row_number() over (partition by id,name,dept order by id) as dup from student) where dup>1);

select * from student;

Note:  Delete operation cannot be performed with rownum because the rownum is a Pseudo column that in assigned in an incremental order after the where clause but before group by or order clause.

Simple Example using ROW ID with out ROW_NUMBER function.

delete from student where rowid not in (select max(rowid) from student group by id,name,dept);