CODEWITHSUNDEEP
sqlsybase
gchq
gchq

Reputation: 1771

SQL DISTINCT based on two variables

I am trying to eliminate duplicate values where both the Customer_ID AND the Rule_Number are the same.. Any idea how that could be accomplished?

Thanks

SELECT Violations_Temp.Temp_ID as 'ID', 
   Customers.Cust_Name as 'Homeowner', 
       Customers.Add1 as 'Unit', 
       Rules_Main.Rule_Number as 'Rule', 
        Violations_Temp.User_Name as 'Inspector', 
        DATE(Violations_Temp.Upload_TimeStamp) as 'Date', 
        Cast(Violations_Temp.Upload_TimeStamp as time) as 'Time'
        FROM Violations_Temp 
        JOIN Customers ON Customers.Customer_ID = Violations_Temp.Customer_ID 
        JOIN Rules_Main ON Rules_Main.Rules_ID = Violations_Temp.Violation_ID

In reply to the message by Loc - you can see from the results that (amongst others) homeowner Adam White has multiple records where the Rule is 11 - I need to set this so that only one record is returned for that combination

Screenshot
(source: hasoftware.ws)

Upvotes: 0

Views: 123

Answers (2)

LHA
LHA

Reputation: 9655

Hope this query work. I used table alias here:

SELECT C.*, T.Upload_TimeStamp, T.User_Name, X.Rule_Number
FROM Customers C,
     Violations_Temp T, 
     (SELECT
            T.Customer_ID,
            R.Rule_Number,
            MAX(T.Upload_TimeStamp) AS MAX_Upload_Timestamp 
      FROM 
        Violations_Temp T,
        Rules_Main R
      WHERE T.Violation_ID = R.Rules_ID
      GROUP BY C.Customer_ID, R.Rule_Number) X
WHERE
T.Customer_ID = C.Customer_ID
AND T.Customer_ID = X.Customer_ID
AND T.Upload_TimeStamp = X.MAX_Upload_Timestamp

Upvotes: 1

gchq
gchq

Reputation: 1771

This may not be the most elegant way to do it but it works... (When the returned data is loaded in a DataTable)...

Dim vHash As New Hashtable()
            Dim vDuplicate As New ArrayList()

            For Each Row As DataRow In PopupDT.Rows
                Dim vSearchString As String = Row("Homeowner") & "_" & Row("Rule")
                If vHash.Contains(vSearchString) Then

                    vDuplicate.Add(Row)
                Else
                    With vHash
                        .Add(vSearchString, String.Empty)
                    End With
                End If
            Next

            For Each Row As DataRow In vDuplicate
                PopupDT.Rows.Remove(Row)
            Next

Upvotes: 0

Related Questions