Mega Code Archive

 

Categories / Delphi / Examples

 

Sql find duplicate rows in a table [with a primary key]

Question: I have a table of city names and need to find duplicate entries. The table does have a primary key called CITY_ID, so the duplicates will have different CITY_ID values but identical CITY_NAME values. Answer: If you indeed have a primary key then you need two cursor instances (c1, c2) as the following example shows. The query requires that ID #1 is smaller than ID #2 otherwise all pairs would be returned twice (2,3) and (3,2) or, if you don't even require that c1 <> c2, .. well, try that out for yourself. If you do not have a primary key defined, see the other tip mentioned in the 'See Also' box. Note: In Microsoft SQL-Server, you can use the HAVING clause, as shown at the bottom. // return all pairs of city IDs that have the same city name select c1.city_id, c2.city_id, c1.city_name from cities c1, cities c2 where c1.city_id < c2.city_id and c1.city_name = c2.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