How can I get records that have a duplicate combination of columns in SQL?

Question

How can I get just the records that have a duplicate combination of columns? For example in the table below I want to get the records that have a duplicate combination of ParentID and Date.

+----+----------+-------------------------+
| ID | ParentID |          Date           |
+----+----------+-------------------------+
|  1 |      225 | 2018-05-10 00:00:00.000 |
|  2 |      225 | 2018-05-10 00:00:00.000 |
|  3 |      300 | 2019-25-11 00:00:00.000 |
+----+----------+-------------------------+

This question was asked a billion times on this site https://stackoverflow.com/questions/2594829/finding-duplicate-values-in-a-sql-table — Nerevar, Jun 04 '18 at 07:08
Add some more sample data, and also specify the expected result. — jarlh, Jun 04 '18 at 07:13
@Nerevar - perhaps I wasn't using the right words to search! This is exactly what I wanted. Thanks! — rohitnair, Jun 04 '18 at 07:19

score 2 · Answer 1 · edited Jun 04 '18 at 07:17

2

The inner select gets the parentID and date of the records having duplicates and the outer select gets the cpmplete data including id

SELECT t1.*
FROM your_table t1
INNER JOIN
(
    SELECT parentId, date
    FROM your_table
    GROUP BY parentId, date
    HAVING COUNT(*) > 1
) t2
    ON t1.parentId = t2.parentId AND t1.date = t2.date;

edited Jun 04 '18 at 07:17

Tim Biegeleisen

502,043
27
286
360

answered Jun 04 '18 at 07:08

juergen d

201,996
37
293
362

Tim Biegeleisen · Answer 2 · 2018-06-04T07:30:31.650

1

The MySQL answer to this question was given by @juergen but here is an option for any database supporting analytic functions:

SELECT ID, ParentID, Date
FROM
(
    SELECT ID, ParentID, Date, COUNT(*) OVER (PARTITION BY ParentID, Date) cnt
    FROM yourTable
) t
WHERE t.cnt > 1;

In general, the above approach using analytic functions would probably outperform the traditional join/aggregation approach. But, this assumes that your database supports the above query.

edited Jun 04 '18 at 07:30

answered Jun 04 '18 at 07:10

Tim Biegeleisen

502,043
27
286
360

Your answer seems to suggest, that MySQL does not support that. Is that really the case? I thought that MySQL 8.0.2 introduced window functions. – Micha Wiedenmann Jun 04 '18 at 07:23
@MichaWiedenmann Yes, it does. But few people are using this version in production (I don't even know if it's available e.g. via RDS). Hence, most MySQL questions sort of assume the version is earlier than 8. – Tim Biegeleisen Jun 04 '18 at 07:25
I would be delighted if you could rephrase your answer such that the hasty copy-reader defaults to analytic functions even when using MySQL. IMHO this is the better default case. – Micha Wiedenmann Jun 04 '18 at 07:28

How can I get records that have a duplicate combination of columns in SQL?

2 Answers2