Way to delete the duplicate records in oracle

Suppose we have the table EMPLOYEE with following table structure

Now in the column EMPLOYEE_FULL_NAME we have duplicate records for JOHN & BRUCE

The query for deleting the duplicate records is below:


DELETE FROM EMPLOYEE A
WHERE
ROWID >
(SELECT MIN(ROWID) FROM EMPLOYEE B
WHERE
b.EMPLOYEE_FULL_NAME = a.EMPLOYEE_FULL_NAME
 );

This query should delete the records which are duplicate for column EMPLOYEE_FULL_NAME.

 Note:
If there are more columns to be included in the duplicate list, then please add those columns near the self join statement , like

DELETE FROM EMPLOYEE A
WHERE
ROWID >
(SELECT MIN(ROWID) FROM EMPLOYEE B
WHERE
b.EMPLOYEE_FULL_NAME = a.EMPLOYEE_FULL_NAME
AND
B.FIRST_NAME = A.FIRST_NAME
     ); 


Hope this makes it clear to delete the duplicate rows.

There are many other ways also, but this one is simplest one.


The other way is:

DELETE FROM EMPLOYEE
WHERE  rowid IN
       ( SELECT LEAD(rowid) OVER
                (PARTITION BY EMPLOYEE_FULL_NAME ORDER BY NULL)
         FROM   EMPLOYEE );

Thanks
Ashish

How to restrict the length of a field in a SELECT query

I was writing the MINUS query, 
In source table the length of the column was 300 characters and in target the length was trimmed to 60 characters.

So here is the solution:

In MYSQL Data Base:

 

SELECT LastName, SUBSTRING(FirstName, 0, 6) AS FIRSTNAME FROM Person.Person

In oracle the syntax is :

SELECT LASTNAME, SUBSTR(FIRSTNAME, 0, 6) AS FIRSTNAME FROM PERSON.PERSON

These queries will restrict the length of the field to 6 characters.

You can modify it as per your convenience.

These two queries are working and have been tested.

 

Suggestions are welcome on this.