Nikolay Nikolay - 2 years ago 79
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

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 Source

This should do:

FROM dbo.YourTable
FROM (  SELECT  project_id,
        FROM dbo.YourTable
        GROUP BY project_id,
        HAVING COUNT(*) > 1 
        AND COUNT(DISTINCT sum_1) = COUNT(*)) B
    ON A.project_id = B.project_id 
    AND A.sum_2 = B.sum_2;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download