Inscrivez-vous ou connectez-vous pour rejoindre votre communauté professionnelle.
REPORTING QUERIES
Select * from Employee a where row_id != select max(row_id) for Employee b where a.Employee_num=b.Employee_num;
.Command Used to fetch records:
Select * from Employee;
Employee_num Employee_name Department 1 Rahul OBIEE 1 Rahul OBIEE 2 Rohit OBIEESo we will start analysing above table.First we need to calculate the records orfetch the records which are dupicate records.
We are again using concept of row_id here.So i am displaying row_ids of theemployees.
select e.*,e.row_id from Employee e;
Employee_num Employee_name Department Row_ID 1 Rahul OBIEE 5001 1 Rahul 5002 2 Rohit OBIEE 5003
Here you will see or analyse that for the duplicate records the row_ids are different.So our logic is fetch the records where the row_id is maximum.But we need to take care of joining condition because we want data for specific group.So in our table we will use Employee_num as condition.
So to Fetch the Duplicate records from table following is the Query:
select a.* from Employee a where rowid != (select max(rowid) from Employee b where a.Employee_num =b.Employee_num;It will fetch following results:
Employee_num Employee_name Department Row_ID 1 Rahul OBIEE 5002
Using Simple delete statement you can remove the duplicate records from the table.
