Nikolay Nikolay - 3 months ago 8
SQL Question

How to find duplicates in SQL Server in one column provided other column the same and third column has not duplicates?

There is no trivial task.

Here is table:

id | project_id | sum_1 | sum_2


Task:


  • Find all records meet all conditions below:


    1. project_id the same and:

    2. sum_2 has duplicates (with same project_id)

    3. sum_1 has NOT duplicates




List all columns of this selection as result:

id | project_id | sum_1 | sum_2


Please help me :)

Answer

This should do:

SELECT A.*
FROM dbo.YourTable
FROM (  SELECT  project_id,
                sum_2
        FROM dbo.YourTable
        GROUP BY project_id,
                 sum_2
        HAVING COUNT(*) > 1 
        AND COUNT(DISTINCT sum_1) = COUNT(*)) B
    ON A.project_id = B.project_id 
    AND A.sum_2 = B.sum_2;