Get instant live expert help with SQL
“My expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Enter your problem description Our Experts are available now

Your message must be at least 40 characters
Select your SQL Environment:
Your message must be at least 40 characters
By submitting this form you agree to Got It's privacy policy.

Oracle SQL Query To Remove Duplicates – Querychat

In this article, we will deal with the problem of duplicates in our tables. We will discover the query to find and remove duplicates in Oracle. Do not be afraid, this task is easier than it seems, we will give the illustrative examples.

Getting started

Let’s create a table with records so that we will be able to make some examples on its base.

This script will create a table called “Companies_Phone” that will contain 3 columns representing the short information about each company, namely, the ID, company name and the company phone number.

CREATE TABLE Companies_Phone (

        ID   NUMBER generated BY DEFAULT AS IDENTITY,

        Company_name VARCHAR2(255),

        Phone VARCHAR2(255)

);

Now we will add new records with some duplicates to the table using this script: 

INSERT INTO Companies_Phone(Company_name,Phone) VALUES('Company_1','111111111111111');

INSERT INTO Companies_Phone(Company_name,Phone) VALUES('Company_1','222222222222222');

INSERT INTO Companies_Phone(Company_name,Phone) VALUES('Company_1','111111111111111');

INSERT INTO Companies_Phone(Company_name,Phone) VALUES('Company_2','333333333333333');

INSERT INTO Companies_Phone(Company_name,Phone) VALUES('Company_2','333333333333333');

INSERT INTO Companies_Phone(Company_name,Phone) VALUES('Company_2','444444444444444');

INSERT INTO Companies_Phone(Company_name,Phone) VALUES('Company_3','555555555555555');

Let’s run a simple select to be sure that our table is ready to uset:

SELECT * FROM Companies_Phone;

Figure 1. “Companies_Phone” data

As we can see, some records have different IDs but the same Company name and Phone number. These are the duplicates we need to remove.

Query to remove duplicates in table

Example 1

Now we will find the last ID  for every Company_name + Phone combination. Then we will delete all the other duplicates.

SELECT MAX(id) FROM Companies_Phone

GROUP BY Company_name,Phone

ORDER BY MAX(id);

Figure 2. Last ID inserted for each combination

Now we need to get rid of similar records. 

To remove the duplicates in a single SQL query we will use this script:

DELETE FROM Companies_Phone

WHERE id NOT IN

  ( SELECT MAX(id) FROM Companies_Phone

    GROUP BY Company_name,Phone

  );

And finally, let’s check the result of our query:

Figure 3. Table data after deleting duplicates

As we can see, the rows with the IDs 1 and 4 were deleted, as we expected and unique records are present in the result set.

In case that we want to leave the lowest IDs of the ID column, we can use the MIN function instead of MAX. 

DELETE FROM Companies_Phone

WHERE id NOT IN

  ( SELECT MIN(id) FROM Companies_Phone

    GROUP BY Company_name,Phone

  );

Example 2

Let’s create another table, it will be called “Companies_Phone_1” but the structure of the table will differ from the previous one. In the first example, we have an automatically generated ID, and in this new table it’s just a numeric column. 

CREATE TABLE Companies_Phone_1 (

        ID   NUMBER,

        Company_name VARCHAR2(255),

        Phone VARCHAR2(255)

);

And then we will insert few rows: 

INSERT INTO Companies_Phone_1(ID, Company_name,Phone) VALUES(1,'Company_1','111111111111111');

INSERT INTO Companies_Phone_1(ID, Company_name,Phone) VALUES(1,'Company_1','111111111111111');

Now we will check the newly-created table:

SELECT * FROM Companies_Phone_1

Figure 4. “Companies_Phone_1” data

Our table consists of two rows of duplicated data with similar IDs. This means that the duplicated are simple copies, so the way of deleting the copies is different. 

We will use the ROWID. It is a pseudocolumn that uniquely defines a single row in a table and this will help us to delete the duplicates in our new table.

DELETE FROM Companies_Phone_1

WHERE ROWID NOT IN

  ( SELECT MIN(ROWID) FROM Companies_Phone_1

    GROUP BY ID,Company_name,Phone

  );

Figure 5. “Companies_Phone_1” table free from duplicates

Instant livechat to an Expert!

Most of the time, the problem you will need to solve will be more complex than a simple method. If you want to save hours of research and frustration, try our live SQLExpert service! 

Our SQLQuerychat Experts are available 24/7 to answer any questions you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Leave a Reply

avatar
  Subscribe  
Notify of
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc