SQL Standard

SQL Find Duplicate Rows

Although SQL provides us with constraints to prevent duplicate data, you may encounter an already existing database with duplicate records.

Using this tutorial, you will discover how to identify duplicate rows within a database.

SQL Find Duplicate

The first method we can use to find duplicate rows is the count function.

Assume we have a table with sample data as shown:

CREATE TABLE products(
    id serial,
    product_name VARCHAR(255),
    quantity INT
);
INSERT INTO products(product_name, quantity)
VALUES ('apples', 100),
        ('oranges', 120),
        ('apples', 100),
        ('bananas', 300),
        ('oranges', 100),
        ('bananas', 300);

The above query should return the table as shown:

To filter for duplicate records, we can use the query as shown below:

SELECT PRODUCT_NAME,
    QUANTITY
FROM PRODUCTS
GROUP BY PRODUCT_NAME,
    QUANTITY
HAVING COUNT(ID) > 1;

The query above uses the group by and count function to search for duplicate records. This should return the output as shown:

The above query works by creating a group of rows with the same values. This is accomplished using the group by clause. We then find out which of the groups has a count greater than 1. This means that there are duplicates in the group.

Terminating

In this article, you discovered how to find duplicate records in SQL using the group by and count clause.

Thanks for reading!!

About the author

John Otieno

My name is John and am a fellow geek like you. I am passionate about all things computers from Hardware, Operating systems to Programming. My dream is to share my knowledge with the world and help out fellow geeks. Follow my content by subscribing to LinuxHint mailing list