SQL: Select duplicate rows, select indistinct rows

Using SQL, MySQL or whatever your favorite database may be, we all know using SELECT DISTINCT will return unique values in case a value appears more than once in a table.

How do you fine duplicates? What if I want to know where the duplicates are?

The trick is to use count() and compare it to an integer.

SELECT count(*), locations.* FROM locations GROUP BY number HAVING COUNT(*) > 1

That will select a count of unique instances of a value from a table called “locations”, group them by a field called “number” where the count is greater than 1. In other words, it will return all the fields where there are more than one instance of the same value in the column “number”.

Did you find this post useful or have questions or comments? Please let me know!

This entry was posted in How Tos, mysql, SQL and tagged , , . Bookmark the permalink.

1 Response to SQL: Select duplicate rows, select indistinct rows

  1. McElwaine fan says:

    Dude, just to let you know, Robert E McElwaine has passed on. He appears to have gone quietly.

    http://www.leadertelegram.com/story-ros.asp?id=BFPEGR72AJU

    An interesting character, no two ways about it.

Leave a Reply

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