Nikolay Nikolay - 6 months ago 24
SQL Question

How to find in MSSQL duplicates 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 fields 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;