ORACLE SQL Group By STILL giving Duplicates

Question

Have been working on this the whole day.

Following query returns around 162,000 records. Handful of them are duplicates.

Really, I'm not sure how else to troubleshoot.

This database pulls from external database for which I only have READ Access.

For example, if I have two rows

20684 2/26/2019 11:13:04 AM abra 123 abc 6 abra

20690 2/26/2019 11:13:04 AM abra 123 abc 6 abra

I would like to keep 20684 abra 123 abc 6 abra

20684 is the uniqueI.INCIDENTIDthat is not included inGROUP BY`

Please assist.

   SELECT TO_CHAR(MIN(I.INCIDENTID))      AS "Incident ID",
          I.CREATIONDATE                  AS "Creation Date",
          M.MESSAGESUBJECT                AS "Email Subject",
          MO.IPADDRESS                    AS "IP Address",
          MO.DOMAINUSERNAME               AS "Login ID",
          MO.ENDPOINTMACHINENAME          AS "Computer Name"
   FROM        MESSAGE M 
   LEFT JOIN   INCIDENT I 
   ON          M.MESSAGESOURCE = I.MESSAGESOURCE 
   AND         M.MESSAGEID = I.MESSAGEID
   AND         M.MESSAGEDATE    = I.MESSAGEDATE 
   LEFT JOIN   MESSAGEORIGINATOR MO
   ON          M.MESSAGEORIGINATORID = MO.MESSAGEORIGINATORID
   LEFT JOIN   MESSAGEEXT ME
   ON          ME.MESSAGEID = M.MESSAGEID
   LEFT JOIN   INCIDENTSTATUS S
   ON          S.INCIDENTSTATUSID = I.INCIDENTSTATUSID
   LEFT JOIN   CUSTOMATTRIBUTESRECORD C
   ON          C.CUSTOMATTRIBUTESRECORDID = I.CUSTOMATTRIBUTESRECORDID                    
   GROUP BY    I.CREATIONDATE,
               M.MESSAGESUBJECT,
               MO.IPADDRESS,
               MO.DOMAINUSERNAME,
               MO.ENDPOINTMACHINENAME

Answer 1

If CREATIONDATE is a date data type it contains the time element down to seconds. It's highly unlikely that the Incident Id's you're looking for the lowest of will have the exact time. Format the date to a ISO Date and the GROUP BY will ignore the time differences.

SELECT TO_CHAR(MIN(I.INCIDENTID))      AS "Incident ID",
          to_char(I.CREATIONDATE, 'yyyy-mm-dd') AS "Creation Date",
          M.MESSAGESUBJECT                AS "Email Subject",
          MO.IPADDRESS                    AS "IP Address",
          MO.DOMAINUSERNAME               AS "Login ID",
          MO.ENDPOINTMACHINENAME          AS "Computer Name"
   FROM        MESSAGE M 
   LEFT JOIN   INCIDENT I 
   ON          M.MESSAGESOURCE = I.MESSAGESOURCE 
   AND         M.MESSAGEID = I.MESSAGEID
   AND         M.MESSAGEDATE    = I.MESSAGEDATE 
   LEFT JOIN   MESSAGEORIGINATOR MO
   ON          M.MESSAGEORIGINATORID = MO.MESSAGEORIGINATORID
   LEFT JOIN   MESSAGEEXT ME
   ON          ME.MESSAGEID = M.MESSAGEID
   LEFT JOIN   INCIDENTSTATUS S
   ON          S.INCIDENTSTATUSID = I.INCIDENTSTATUSID
   LEFT JOIN   CUSTOMATTRIBUTESRECORD C
   ON          C.CUSTOMATTRIBUTESRECORDID = I.CUSTOMATTRIBUTESRECORDID                    
   GROUP BY    to_char(I.CREATIONDATE, 'yyyy-mm-dd') 
               M.MESSAGESUBJECT,
               MO.IPADDRESS,
               MO.DOMAINUSERNAME,
               MO.ENDPOINTMACHINENAME