MySQL Select Count Distinct – Counting and Identifying Duplicate Records


Looking for a [relatively] quick way to identify duplicate records in a table and list how many records there are for each duplicate? Look no further.

In my case, I’ve got a table named Searches which tracks queries on one of my sites. I wanted to check the top queries, so I used this SQL statement:

SELECT COUNT(*) AS repetitions, Query
FROM Searches
HAVING repetitions > 1
ORDER BY repetitions DESC;

There you have it. A simple, effective way to count and label duplicate records.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>