Nikolay Nikolay - 3 months ago 8
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;
Comments