Duplicates Removal Using Group By, Rank, Row_number

I have two tables. One is CustomerOrders and the other is OrderCustomerRef - lookup table. Both tables have one-to-many relationship - one customer may be associated with multiple

Solution 1:

Based on what information you provided, I used the following CTE to show the results that look to get what you want:

WITH DaCTE -- To rank the existing rowsAS  (
        SELECT pk_ID
            , cust_ID
            , fname
            , lname
            , [customer_e-mail]
            , Order_Date
            , Customer_Source
            , customertype
            , ROW_NUMBER() OVER (PARTITIONBY fname, lname, [customer_e-mail] ORDERBY customertype DESC, order_date DESC, cust_id) as RankYo -- Orders by the criteria provided but while you suggested 3 should lose to 5, they have the same criteria so either one could win based on orderingFROM #customerorders
    )
, NewSource -- To show winning Customer ID next to Original IDAS  (
        SELECT co.pk_ID
            , DaCTE.cust_ID as NewCustomerID
            , co.cust_ID as OriginalCustomerID
            , co.fname
            , co.lname
            , co.[customer_e-mail]
            , co.Order_Date
            , co.Customer_Source
            , co.customertype
        FROM DaCTE
        INNERJOIN #CustomerOrders as co
            ON co.fname = DaCTE.FName
            AND co.lname = DaCTE.LName
            AND co.[customer_e-mail] = DaCTE.[Customer_E-mail]
        WHERE DaCTE.RankYo =1-- filter to show only the winning IDs based on resulting rank from previous CTE
    )
SELECT*/*UPDATE ocr --commented out so you can see the results before running update
SET ocr.Cust_ID = ns.NewCustomerID*/FROM #OrderCustomerRef as ocr
INNERJOIN NewSource as ns
    ON ns.OriginalCustomerID = ocr.Cust_ID

Solution 2:

you can try this using CTE(Common Table Expression) as explained Antoine Hernandez in the above answer and you also can remove duplicates from the table using UNION And EXCEPT Operator.

i.g Using EXCEPT Operator

SELECT*FROM  #customerorders
EXCEPTSELECT*FROM  #customerorders WHERE1=0

i.g Using UNION Operator

SELECT*FROM  #customerorders
UNIONSELECT*FROM  #customerorders WHERE1=0

For More Information about How to Remove Duplicates Using CTE Please follow this link : Remove Duplicates Using CTE

For More Information about How to Remove Duplicates Using UNION And EXCEPT Operator Please follow this link : Remove Duplicates Using UNION And EXCEPT Operator