SQL > SQL - solving issues > Finding duplicates

SQL: Finding duplicates

A simple way finding duplicates on a selected field as e.g. the "Id" field:

DuplicatesSource
From this statement including a simple subquery

SELECT
  id
, FirstName
, LastName
FROM "Names"
WHERE id IN
(
  SELECT
    id
  FROM "Names"
  GROUP BY id
  HAVING
    COUNT(id) > 1
)
ORDER BY id;

you will get a list of records with the duplicate "id" field values:

DuplicatesSolved

You may also write the statement using a correlated subquery like:

SELECT
  T.id
, T.FirstName
, T.LastName
FROM "Names" T
WHERE EXISTS
(
  SELECT
    Tdup.id
  FROM "Names" AS Tdup
  WHERE Tdup.id = T.id
  GROUP BY Tdup.id
  HAVING
    COUNT(Tdup.id) > 1
)
ORDER BY T.id;

working equally well on small datasets, but which on large tables will be significantly slower, and maybe even with a response like this:

Duplicates-EXISTS-not-applicable

SQL: Finding duplicates including the duplicate counts

If you want you to also get the duplicate count numbers, with Paradox you will need to use a JOIN design using a disc stored SQL statement, as BDE/Paradox does not provide temporary MEMORY tables or SQL VIEWs and has only limited capabilities using subqueries.

First you will need to create an SQL like this:
SELECT
id
, COUNT(id) AS IdCounts
FROM "Names"
GROUP BY
id
HAVING
COUNT(id) > 1
;
and save it as e.g. Names-IdDups.sql. You may store it in a sub folder .\SQL relative to the database directory.

Next, you will create an SQL statement like this joining the Names-IdDups.sql to get also the duplicate count numbers:
SELECT DISTINCT
T.Id
, Tdup.IdCounts
FROM "Names" AS T
JOIN "SQL\Names-IdDups.sql" AS Tdup
ON T.Id = Tdup.Id
ORDER BY T.Id ;

and you will get this:

DuplicatesSolved-DISTINCT

or for the full list of duplicate field records:

SELECT
T.Id
, Tdup.IdCounts
, T.FirstName
, T.LastName
FROM "Names" AS T
JOIN "SQL\Names-IdDups.sql" AS Tdup
ON T.Id = Tdup.Id
ORDER BY T.Id ;

DuplicatesSolved-wCounts