In database management, ensuring the uniqueness of certain fields is crucial for maintaining data integrity. However, sometimes duplicates can sneak in, especially in large datasets. If you have a MySQL table with a column that might contain duplicate entries and you want to list all rows where the content is duplicated, this guide is for you.

Scenario

Let’s assume you have a table named notes with a column note_details. You want to find all rows where the note_details content is duplicated.

The SQL Query

To identify duplicate entries in the note_details column, you can use a combination of the GROUP BY and HAVING clauses in your SQL query. Here's the query you need:

SELECT note_details, COUNT(*) AS count
FROM notes
GROUP BY note_details
HAVING count > 1;

Explanation

• SELECT note_details, COUNT(*) AS count: This part of the query selects the note_details and counts the number of occurrences of each note_details value.
• FROM notes: Specifies the table from which to retrieve the data.
• GROUP BY note_details: Groups the results by the note_details column, so that duplicate values are grouped together.
• HAVING count > 1: Filters the grouped results to include only those groups where the count of note_details is greater than 1, indicating duplicates.

Example

Let’s say you have the following data in your notes table:

Running the above query will return:

This result shows that the note_details "Meeting at 10 AM" and "Buy groceries" are duplicated, each appearing twice in the table.

Retrieving Full Rows of Duplicates

If you need to retrieve the full rows of these duplicates, you can join this result with the original table:

SELECT n.*
FROM notes n
JOIN (
    SELECT note_details
    FROM notes
    GROUP BY note_details
    HAVING COUNT(*) > 1
) dup ON n.note_details = dup.note_details
ORDER BY n.note_details;