CODEWITHSUNDEEP
sqlsql-servert-sql
Martin Lebel
Martin Lebel

Reputation: 507

Select a NON-DISTINCT column in a query that return distincts rows

The following query returns the results that I need but I have to add the ID of the row to then update it. If I add the ID directly in the select statement it will return me more results then I need because each ID is unique so the DISTINCT statement see the line as unique.

SELECT DISTINCT ucpse.MemberID, ucpse.ProductID, ucpse.UserID
FROM UserCustomerProductSalaryExceptions as ucpse
WHERE EXISTS (SELECT NULL 
            FROM UserCustomerProductSalaryExceptions as upcse2 
            WHERE ucpse.userid = upcse2.userid AND ucpse.MemberID = upcse2.MemberID AND ucpse.ProductID = upcse2.ProductID
            GROUP BY upcse2.UserID, upcse2.memberid, upcse2.productid 
            HAVING COUNT(UserID) >= 2
            )

So basically I need to add ucpse.ID in the Select statement while keeping DISTINCT values for MemberID,ProductID and UserID.

Any Ideas ?

Thank you

Upvotes: 0

Views: 3025

Answers (4)

Sam
Sam

Reputation: 111

If all you want is to delete the duplicates, this will do it:

WITH X AS
(SELECT ID,
ROW_NUMBER() OVER (PARTITION BY MemberID, ProductID, UserID ORDER BY ID) AS DupRowNum<br
FROM UserCustomerProductSalaryExceptions
)
DELETE X WHERE DupRowNum > 1

Upvotes: 1

Dimt
Dimt

Reputation: 2328

According to you comment: If the data has been duplicated 67 times for a given employee with a given product and a given client, I need to keep only one of thoses records. It's not important which one, so this is why I use DISTINC to obtain unique combinaison of given employee with a given product and a given client.

You can use MIN() or MAX() and GROUP BY instead of DISTINCT 

SELECT MAX(ucpse.ID) AS ID, ucpse.MemberID, ucpse.ProductID, ucpse.UserID
FROM UserCustomerProductSalaryExceptions as ucpse
WHERE EXISTS (SELECT NULL 
            FROM UserCustomerProductSalaryExceptions as upcse2 
            WHERE ucpse.userid = upcse2.userid AND ucpse.MemberID = upcse2.MemberID AND ucpse.ProductID = upcse2.ProductID
            GROUP BY upcse2.UserID, upcse2.memberid, upcse2.productid 
            HAVING COUNT(UserID) >= 2
            )

GROUP BY ucpse.MemberID, ucpse.ProductID, ucpse.UserID

UPDATE:

From you comments I think the below query is what you need

DELETE FROM  UserCustomerProductSalaryExceptions
WHERE ID NOT IN  ( SELECT MAX(ucpse.ID) AS ID
                         FROM #UserCustomerProductSalaryExceptions
                         GROUP BY ucpse.MemberID, ucpse.ProductID, ucpse.UserID
                         HAVING COUNT(ucpse.ID) >= 2
                     )

Upvotes: 3

Patrick Tucci
Patrick Tucci

Reputation: 1952

From the sound of your data structure (which I would STRONGLY advise normalizing as soon as possible), it sounds like you should be updating all the records. It sounds as if each duplicate is important because it contains some information about an employee's relation to a customer or product.

I would probably update all the records. Try this:

UPDATE UCPSE 
SET
    --Do your updates here
FROM UserCustomerProductSalaryExceptions as ucpse
JOIN
(
    SELECT UserID, MemberID, ProductID
    FROM UserCustomerProductSalaryExceptions
    GROUP BY UserID, MemberID, ProductID
    HAVING COUNT(UserID) >= 2
) T
ON ucpse.UserID = T.UserID AND ucpse.MemberID = T.MemberID AND ucpse.ProductID = T.ProductID

Upvotes: 0

bwperrin
bwperrin

Reputation: 692

ID's not necessary - try:

UPDATE uu SET
    <your settings here>
FROM UserCustomerProductSalaryExceptions uu
    JOIN ( <paste your entire query above here> 
         ) uc ON uc.MemberID=uu.MemberId AND uc.ProductID=uu.ProductId AND uc.UserID=uu.UserId

Upvotes: 0

Related Questions