Find duplicate rows in a table (no primary key)

21 comments. Current rating: (8 votes). Leave comments and/ or rate it.

Question:

I have a table of city names and need to find duplicate entries. The table does not have a primary key, so the duplicate rows are entirely identical.

Answer:

You can use the GROUP clause in your SELECT statement. Below are 3 different attempts. Try the first one first. It may not work with your DBMS but if it works, it's most convenient. The second one is an ok choice as it returns all rows but the duplicate rows in the 'See Also' box.

The last version returns all city names with a count value and you manually have to look for those with a value > 1. If you need to do this more often, you should consider creating a VIEW or a temporary table with this query and then do a SELECT on this VIEW/ temp table.

In Microsoft SQL-Server, you can use the HAVING clause, as shown at the bottom.
And: If you do have a primary key defined, see the other tip mentioned at the top. This would work in that situation, but using the unique ID is better.

	// Return all duplicate cities and how often they appear // Works with ORACLE select city_name, count(city_name) as cnt from areas group by city_name where cnt>1 // not all SQL dbms will support the reference to the count column cnt in the where clause. // The following will return ALL rows with counter, but sorted by number of appearances // Your duplicates will be at the top. // Works with MYSQL select city_name, count(city_name) as cnt from areas group by city_name order by cnt desc // finally, no back reference to count column cnt at all- // the following will work on all SQL dbms: // Return all cities and how often they appear select city_name, count(city_name) as cnt from areas group by city_name // version for Micrsoft's MSSQL Server // make use of the HAVING clause select city_name from areas group by city_name having count(*) > 1

Comments:

You are on page 2 of 2, other pages: 1 [2]

2008-07-08, 07:35:23

anonymous from Oulu, Finland

Thanks guys, really helpful.

2008-07-19, 08:17:35

[hidden]

This would also work: SELECT city_name, COUNT(city_name) AS NumOccurrences FROM areas GROUP BY city_name HAVING ( COUNT(city_name) > 1 ) // By =IceBurn= //

2008-12-16, 05:39:54

anonymous from Malaysia

Vary Good Support and Help

2009-01-08, 11:08:33

anonymous from United States

Add a sequential primary key by using the ALTER TABLE statement To add a column to an existing table, the ALTER TABLE syntax is: ALTER TABLE table_name ADD column_name column-definition; For example: ALTER TABLE supplier ADD supplier_name varchar2(50); This will add a column called supplier_name to the supplier table.

2009-09-08, 08:00:48

anonymous from India

Keywords:

2009-10-24, 02:29:43

anonymous from India

Very Useful. Thanks.

You are on page 2 of 2, other pages: 1 [2]

NEW: Optional: Register   Login Email address (not necessary): Rate as Off-topic Very Helpful Helpful Ok article Not useful Very bad	Hide my email when showing my comment. Please notify me once a day about new comments on this topic. Please provide a valid email address if you select this option, or post under a registered account.
	Show city and country Show country only Hide my location
You can mark text as 'quoted' by putting [quote] .. [/quote] around it.
Please type in the code:
Add a picture: Please do not post inappropriate pictures. Inappropriate pictures include pictures of minors and nudity. The owner of this web site reserves the right to delete such material.