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

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
 

Generated 0:01:10 on Jul 22, 2018