CODEWITHSUNDEEP
mysqlsql
ewok
ewok

Reputation: 21443

SQL: Select all entries where a particular set of fields is duplicated in the table

I need a query to select all entries from all rows, where a particular 2 column pair is duplicated. So, for instance, if my table is (id int, Project varchar, Version varchar, Deployer varchar, Date DateTime), I want to get all rows in which there is a duplicate Project/Version. I've done this

SELECT *
FROM Deployments
GROUP BY Project, Version
HAVING count(*) > 1;

But that only seems to get me the first row for each Project/Version pair. I want to get all rows.

How do I accomplish this?

Upvotes: 1

Views: 95

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

Of course, window functions are an easy method:

select d.*
from (select d.*, count(*) over (partition by project, version) as cnt
      from deployments d
     ) d
where cnt >= 2

Upvotes: 0

nbk
nbk

Reputation: 49373

Use a join where you join your grouped data to select only rows that fit.

In my case which have the same project number and Version

SELECT *
FROM Deployments d INNER JOIN 
(SELECT Project, Version
FROM Deployments
GROUP BY Project, Version
HAVING count(*) > 1) dd ON dd.Project = d.Project AND dd.Version = d.Version;

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520918

Using exists logic is one way to do this:

SELECT d1.*
FROM Deployments d1
WHERE EXISTS (SELECT 1 FROM Deployments d2
              WHERE d2.id <> d1.id AND
                    d1.Project = d2.Project AND d1.Version = d2.Version);

Your current query, which uses aggregation, is off, because it uses SELECT * with GROUP BY. A valid version might be:

SELECT d1.*
FROM Deployments d1
INNER JOIN
(
    SELECT Project, Version
    FROM Deployments
    GROUP BY Project, Version
    HAVING COUNT(*) > 1
) d2
    ON d1.Project = d2.Project AND
       d1.Version = d2.Version;

Upvotes: 0

Related Questions