DelphiFAQ Home Search:
General :: Programming :: Delphi :: Database
Database development with Delphi. Includes issues with BDE, ADO and InterBase.

Articles:

This list is sorted by recent document popularity (not total page views).
New documents will first appear at the bottom.

Only the 40 most recently viewed articles are shown.
You can see the full list here.

Featured Article

Find duplicate rows in a table (no primary key)

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
 
 

Generated 0:01:34 on Oct 22, 2017