Eliminating Duplicate Data
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Eliminating Duplicate Data

  1. #1
    Join Date
    Aug 2003
    Posts
    13

    Eliminating Duplicate Data

    I'm having problems eliminating duplicate data. I have two tables that creates a row of data each time the record is updated. So what I did was add two subqueries and added the max function to bring back the latest row of data. I also added the distinct keyword to bring back only one row of data also. I'm at a loss. Any help is definitely appreciated. See query below:

    SELECT DISTINCT v.VET_SOCIAL_SECURITY_NUMBER,
    V.VET_GIVEN_NAME,V.VET_MIDDLE_NAME,VET_FAMILY_NAME,
    V.VET_DEATH_DATE, V.GENDER_TYPE_CODE, a.ADDRESS_LINE_1, a.ADDRESS_LINE_2,
    a.ADDRESS_CITY_TEXT, a.STATE_CODE, a.ADDRESS_POSTAL_CODE_TEXT,
    WS.WORKLOAD_STAT_TYPE_CODE, WS.WORKLOAD_STATUS_UPDATE_DATE,
    wal.WORKLOAD_ACTIVITY_CODE,
    ct.COMMS_TEMPLATE_FORM_NUMBER, cl.COMMS_LOG_MAILING_DATE
    FROM VETERAN V, PATIENT P, WORKLOAD W, WORKLOAD_STATUS
    WS,workload_activity_link wal,
    address a, comms_log cl, comms_template ct
    WHERE v.VETERAN_IDENTIFIER = a.VETERAN_IDENTIFIER AND V.VETERAN_IDENTIFIER
    = P.VETERAN_IDENTIFIER AND
    P.PATIENT_IDENTIFIER = W.PATIENT_IDENTIFIER AND
    W.WORKLOAD_IDENTIFIER = WS.WORKLOAD_IDENTIFIER AND
    ws.WORKLOAD_STATUS_IDENTIFIER = al.WORKLOAD_STATUS_IDENTIFIER and
    w.WORKLOAD_IDENTIFIER = cl.WORKLOAD_IDENTIFIER and
    cl.COMMS_TEMPLATE_IDENTIFIER = ct.COMMS_TEMPLATE_IDENTIFIER and
    ws.WORKLOAD_STATUS_UPDATE_DATE = (SELECT MAX (WS.WORKLOAD_STATUS_UPDATE_DATE)
    FROM WORKLOAD_STATUS WS
    WHERE WS.WORKLOAD_STATUS_IDENTIFIER= WAL.WORKLOAD_STATUS_IDENTIFIER) and
    v.VET_DEATH_DATE is null and
    ct.COMMS_TEMPLATE_FORM_NUMBER = '742-200' and
    ws.WORKLOAD_STATUS_UPDATE_DATE between '01-MAR-04' and '01-SEP-04'
    AND WS.WORKLOAD_STAT_TYPE_CODE = '04' AND
    wal.WORKLOAD_ACTIVITY_CODE = any ('20', '21') and
    v.VETERAN_IDENTIFIER = a.VETERAN_IDENTIFIER and
    a.ADDRESS_START_DATE = (SELECT MAX (A.ADDRESS_START_DATE)
    from address a
    where v.VETERAN_IDENTIFIER = a.VETERAN_IDENTIFIER) and
    rownum <1100

  2. #2
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    Try this:
    SELECT v.VET_SOCIAL_SECURITY_NUMBER,
    V.VET_GIVEN_NAME,V.VET_MIDDLE_NAME,VET_FAMILY_NAME,
    V.VET_DEATH_DATE, V.GENDER_TYPE_CODE, a.ADDRESS_LINE_1, a.ADDRESS_LINE_2,
    a.ADDRESS_CITY_TEXT, a.STATE_CODE, a.ADDRESS_POSTAL_CODE_TEXT,
    WS.WORKLOAD_STAT_TYPE_CODE, WS.WORKLOAD_STATUS_UPDATE_DATE,
    wal.WORKLOAD_ACTIVITY_CODE,
    ct.COMMS_TEMPLATE_FORM_NUMBER, cl.COMMS_LOG_MAILING_DATE
    FROM VETERAN V, PATIENT P, WORKLOAD W, WORKLOAD_STATUS
    WS,workload_activity_link wal,
    address a, comms_log cl, comms_template ct,
    (SELECT WS.WORKLOAD_STATUS_IDENTIFIER, MAX (WS.WORKLOAD_STATUS_UPDATE_DATE) WORKLOAD_STATUS_UPDATE_DATE
    FROM WORKLOAD_STATUS WS group by WS.WORKLOAD_STATUS_IDENTIFIER) iws,
    (SELECT a.VETERAN_IDENTIFIER, MAX (A.ADDRESS_START_DATE) ADDRESS_START_DATE from address a
    group by a.VETERAN_IDENTIFIER) iad
    WHERE v.VETERAN_IDENTIFIER = a.VETERAN_IDENTIFIER
    AND V.VETERAN_IDENTIFIER = P.VETERAN_IDENTIFIER
    AND P.PATIENT_IDENTIFIER = W.PATIENT_IDENTIFIER
    AND W.WORKLOAD_IDENTIFIER = WS.WORKLOAD_IDENTIFIER
    AND ws.WORKLOAD_STATUS_IDENTIFIER = al.WORKLOAD_STATUS_IDENTIFIER
    and w.WORKLOAD_IDENTIFIER = cl.WORKLOAD_IDENTIFIER
    and cl.COMMS_TEMPLATE_IDENTIFIER = ct.COMMS_TEMPLATE_IDENTIFIER
    and ws.WORKLOAD_STATUS_UPDATE_DATE = iws.WORKLOAD_STATUS_UPDATE_DATE
    and v.VET_DEATH_DATE is null
    and ct.COMMS_TEMPLATE_FORM_NUMBER = '742-200'
    and ws.WORKLOAD_STATUS_UPDATE_DATE between '01-MAR-04' and '01-SEP-04'
    AND WS.WORKLOAD_STAT_TYPE_CODE = '04'
    AND wal.WORKLOAD_ACTIVITY_CODE = any ('20', '21')
    and v.VETERAN_IDENTIFIER = a.VETERAN_IDENTIFIER
    and a.ADDRESS_START_DATE = iad.ADDRESS_START_DATE
    and rownum <1100
    Cheers!
    OraKid.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width