What's the most complex sql query you have ever wrote?

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 OBIEE

  So 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.