How to remove duplicate entries in my query?

Question

The following code gives me multiple lines since there can be more than one Cust_Edit_Log.Edit_Timestamp per Alarm Account. There is no other way for a duplicate to occur. How do I only get the result with the earliest Cust_Edit_Log.Edit_Timestamp date? Thank you in advance for any help you can provide.

Select
AR_Customer.Customer_Number As 'Customer_Number',
AR_Customer.Customer_Name As 'Customer_Name',
AR_Customer_System.Alarm_Account As 'Alarm_Account',
AR_Customer_Site.Address_1 As 'Site_Address_1',
Cust_Edit_Log.UserComments As 'Edit_Log_Cust_User_Comments',
Cust_Edit_Log.Edit_Timestamp As 'Edit_Log_Cust_Timestamp',
Cust_Edit_Log.UserCode As 'Edit_Log_Cust_User'
From
AR_Customer
Inner JOIN AR_Customer_Site On AR_Customer.Customer_Id = AR_Customer_Site.Customer_Id
Left Outer JOIN AR_Customer_System On AR_Customer_Site.Customer_Site_Id = AR_Customer_System.Customer_Site_Id
Left Outer Join CQB_Log_Parse Cust_Edit_Log on AR_Customer.Customer_Id = Cust_Edit_Log.Customer_Id
Where
AR_Customer.Customer_Id <> 1 And
(AR_Customer_System.Alarm_Account Like 'IN%' And
Cust_Edit_Log.UserComments Like 'Edited Customer System IN%')
Order By
AR_Customer.Customer_Number ASC

http://stackoverflow.com/questions/18932/how-can-i-remove-duplicate-rows?rq=1 — apomene, Sep 13 '16 at 11:33

score 0 · Answer 1 · answered Sep 13 '16 at 11:37

Use Partition BY:

SELECT
    X.*
FROM
(
    Select
    AR_Customer.Customer_Number As 'Customer_Number',
    AR_Customer.Customer_Name As 'Customer_Name',
    AR_Customer_System.Alarm_Account As 'Alarm_Account',
    AR_Customer_Site.Address_1 As 'Site_Address_1',
    Cust_Edit_Log.UserComments As 'Edit_Log_Cust_User_Comments',
    Cust_Edit_Log.Edit_Timestamp As 'Edit_Log_Cust_Timestamp',
    Cust_Edit_Log.UserCode As 'Edit_Log_Cust_User',
    ROW_NUMBER() OVER(Partition BY AR_Customer_System.Alarm_Account,Cust_Edit_Log.Edit_Timestamp ORDER BY AR_Customer_System.Alarm_Account) AS PartNO
    From
    AR_Customer
    Inner JOIN AR_Customer_Site On AR_Customer.Customer_Id = AR_Customer_Site.Customer_Id
    Left Outer JOIN AR_Customer_System On AR_Customer_Site.Customer_Site_Id = AR_Customer_System.Customer_Site_Id
    Left Outer Join CQB_Log_Parse Cust_Edit_Log on AR_Customer.Customer_Id = Cust_Edit_Log.Customer_Id
    Where
    AR_Customer.Customer_Id <> 1 And
    (AR_Customer_System.Alarm_Account Like 'IN%' And
    Cust_Edit_Log.UserComments Like 'Edited Customer System IN%')
)X
WHERE X.PartNo=1
Order By X.Customer_Number ASC

score 0 · Answer 2 · answered Sep 13 '16 at 11:38

One method uses row_number():

Left Outer Join
(select lp.*,
        row_number() over (partition by lp.Customer_Id
                           order by Edit_Timestamp asc
                          ) as seqnum
 from CQB_Log_Parse lp
) Cust_Edit_Log
on AR_Customer.Customer_Id = Cust_Edit_Log.Customer_Id and
   seqnum = 1

score 0 · Answer 3 · answered Sep 13 '16 at 11:38

0

Maybe try with MIN(Cust_Edit_Log.Edit_Timestamp)

answered Sep 13 '16 at 11:38

turelson

11
1

score 0 · Answer 4 · answered Sep 13 '16 at 11:41

You can try using as below:

;with cte as (
Select
AR_Customer.Customer_Number As 'Customer_Number',
AR_Customer.Customer_Name As 'Customer_Name',
AR_Customer_System.Alarm_Account As 'Alarm_Account',
AR_Customer_Site.Address_1 As 'Site_Address_1',
Cust_Edit_Log.UserComments As 'Edit_Log_Cust_User_Comments',
Cust_Edit_Log.Edit_Timestamp As 'Edit_Log_Cust_Timestamp',
Cust_Edit_Log.UserCode As 'Edit_Log_Cust_User'
,row_number() over(partition by AR_Customer.Customer_Number order by Cust_Edit_Log.Edit_Timestamp) as rownum
From
AR_Customer
Inner JOIN AR_Customer_Site On AR_Customer.Customer_Id = AR_Customer_Site.Customer_Id
Left Outer JOIN AR_Customer_System On AR_Customer_Site.Customer_Site_Id = AR_Customer_System.Customer_Site_Id
Left Outer Join CQB_Log_Parse Cust_Edit_Log on AR_Customer.Customer_Id = Cust_Edit_Log.Customer_Id
Where
AR_Customer.Customer_Id <> 1 And
(AR_Customer_System.Alarm_Account Like 'IN%' And
Cust_Edit_Log.UserComments Like 'Edited Customer System IN%')
--Order By
--AR_Customer.Customer_Number ASC
)
select * from cte where rownum = 1 
order by AR_Customer.Customer_Number ASC

How to remove duplicate entries in my query?

4 Answers4