Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2003
    Posts
    16

    Unanswered: Removing Duplicates

    Code:
    SELECT DISTINCT TOPS.CNTR.CNTR_NBR,TOPS.CNTR.CREATE_DTTM 
    FROM TOPS.CNTR, TOPS.VESSEL_CALL, TOPS.NOMINATED_VSL, TOPS.BERTHING, TOPS.DG_APPROVAL 
    WHERE (TOPS.CNTR.DISC_VV_CD=TOPS.VESSEL_CALL.VV_CD)
    AND (TOPS.VESSEL_CALL.VV_CD = TOPS.BERTHING.VV_CD)
    AND (TOPS.CNTR.NOM_LOAD_VV_CD = TOPS.NOMINATED_VSL.NOM_VV_CD(+))
    AND (TOPS.CNTR.CNTR_SEQ_NBR = TOPS.DG_APPROVAL.CNTR_SEQ_NBR(+))
    AND (TOPS.VESSEL_CALL.VSL_NM='DAXIN')
    AND (TOPS.VESSEL_CALL.IN_VOY_NBR='009')
    AND (TOPS.CNTR.CNTR_OPR_CD='KL') 
    ORDER BY TOPS.CNTR.CNTR_NBR
    Currently the above query returns 18 records. Its suppose to be only 16.

    CNTR_NBR CREATE_DTTM
    ======= =========
    KKFU1050745 12/27/2003 12:57:09 PM
    KKFU1087960 12/27/2003 12:57:11 PM
    KKFU1087960 12/27/2003 7:26:46 PM
    KKFU1108451 12/27/2003 12:57:13 PM
    KKFU1149487 12/27/2003 12:57:15 PM
    KKTU7085471 12/27/2003 12:57:16 PM
    KKTU7405169 12/27/2003 12:57:18 PM
    KKTU7418063 12/27/2003 12:57:20 PM
    KLFU1255203 12/27/2003 12:57:21 PM
    KLFU1287936 12/27/2003 12:57:23 PM
    KLFU1318960 12/27/2003 12:57:25 PM
    KLFU1345652 12/27/2003 12:57:26 PM
    KLFU1940491 12/27/2003 1:40:04 PM
    KLFU1940491 12/27/2003 7:36:51 PM
    KLTU1132754 12/27/2003 12:57:29 PM
    KLTU7660764 12/27/2003 12:57:30 PM
    TRIU2351579 12/27/2003 12:58:29 PM
    TRIU4358931 12/27/2003 12:58:31 PM

    There are duplicates for the cntr_nbr part, Any way I can just retrieve the records that are most recent only?

    Duplicates :

    KKFU1087960 12/27/2003 12:57:11 PM
    KKFU1087960 12/27/2003 7:26:46 PM

    KLFU1940491 12/27/2003 1:40:04 PM
    KLFU1940491 12/27/2003 7:36:51 PM

    Thanks

  2. #2
    Join Date
    Aug 2003
    Posts
    123
    use the following query and let me know if it works.

    SELECT DISTINCT TOPS.CNTR.CNTR_NBR,
    to_char(TOPS.CNTR.CREATE_DTTM,' YYYY-MM-DD') CREATE_DATE
    FROM TOPS.CNTR, TOPS.VESSEL_CALL, TOPS.NOMINATED_VSL, TOPS.BERTHING, TOPS.DG_APPROVAL
    WHERE (TOPS.CNTR.DISC_VV_CD=TOPS.VESSEL_CALL.VV_CD)
    AND (TOPS.VESSEL_CALL.VV_CD = TOPS.BERTHING.VV_CD)
    AND (TOPS.CNTR.NOM_LOAD_VV_CD = TOPS.NOMINATED_VSL.NOM_VV_CD(+))
    AND (TOPS.CNTR.CNTR_SEQ_NBR = TOPS.DG_APPROVAL.CNTR_SEQ_NBR(+))
    AND (TOPS.VESSEL_CALL.VSL_NM='DAXIN')
    AND (TOPS.VESSEL_CALL.IN_VOY_NBR='009')
    AND (TOPS.CNTR.CNTR_OPR_CD='KL')
    ORDER BY TOPS.CNTR.CNTR_NBR

  3. #3
    Join Date
    Nov 2003
    Posts
    16
    Thanks. Works but I actually need the time for the flatfile that I'm generating. It did remove the 2 records, but when i added more tables, the records went from 18 to 34.

    Code:
    SELECT DISTINCT TOPS.VESSEL_CALL.VSL_NM, 
    TOPS.VESSEL_CALL.IN_VOY_NBR, 
    TOPS.BERTHING.ATB_DTTM, 
    TOPS.VESSEL_CALL.VSL_BERTH_DTTM, 
    TOPS.CNTR.CNTR_NBR, 
    TOPS.CNTR.DISC_SLOT_OPR_CD, 
    TOPS.CNTR.CNTR_OPR_CD, 
    TOPS.CNTR.PURP_CD, 
    TOPS.CNTR.ISO_SIZE_TYPE_CD, 
    TOPS.CNTR.SIZE_FT, 
    TOPS.CNTR.STATUS, 
    TOPS.CNTR.SPECIAL_DETAILS, 
    TOPS.CNTR.REFR_TEMP, 
    TOPS.CNTR.REFR_IND, 
    TOPS.CNTR.IMDG_CL_CD, 
    TOPS.CNTR.DG_IND, 
    TOPS.CNTR.PLOAD, 
    TOPS.CNTR.PDISC1, 
    TOPS.CNTR.PDEST, 
    TOPS.CNTR.DECLR_WT, 
    TOPS.NOMINATED_VSL.VSL_NM AS EXPR1, 
    TOPS.NOMINATED_VSL.OUT_VOY_NBR, TOPS.NOMINATED_VSL.ETB_DTTM, 
    TOPS.NOMINATED_VSL.ETA_DTTM, TOPS.VESSEL_CALL.VSL_ETD_DTTM, 
    to_char(TOPS.CNTR.CREATE_DTTM,' YYYY-MM-DD') CREATE_DTTM, TOPS.CNTR.CREATE_ORG_CD, 
    TOPS.DG_APPROVAL.MPA_GROUP
    FROM TOPS.CNTR, 
    TOPS.VESSEL_CALL, 
    TOPS.NOMINATED_VSL, 
    TOPS.BERTHING, 
    TOPS.DG_APPROVAL 
    WHERE (TOPS.CNTR.DISC_VV_CD=TOPS.VESSEL_CALL.VV_CD)
    AND (TOPS.VESSEL_CALL.VV_CD = TOPS.BERTHING.VV_CD)
    AND (TOPS.CNTR.NOM_LOAD_VV_CD = TOPS.NOMINATED_VSL.NOM_VV_CD(+))
    AND (TOPS.CNTR.CNTR_SEQ_NBR = TOPS.DG_APPROVAL.CNTR_SEQ_NBR(+))
    AND (TOPS.VESSEL_CALL.VSL_NM='DAXIN')
    AND (TOPS.VESSEL_CALL.IN_VOY_NBR='009')
    AND (TOPS.CNTR.CNTR_OPR_CD='KL') 
    ORDER BY TOPS.CNTR.CNTR_NBR
    Last edited by zedd; 12-29-03 at 03:29.

  4. #4
    Join Date
    Aug 2003
    Posts
    123
    use to_char(TOPS.CNTR.CREATE_DTTM,'YYYY-MM-DD HH:MM)
    in place of to_char(TOPS.CNTR.CREATE_DTTM,' YYYY-MM-DD')


    mail me the output

    --Jaggu

  5. #5
    Join Date
    Nov 2003
    Location
    Rotterdam, Netherlands
    Posts
    127
    You can also use MAX(ROWID) group by cntr_nbr

  6. #6
    Join Date
    Nov 2003
    Posts
    16
    Originally posted by shelva
    You can also use MAX(ROWID) group by cntr_nbr
    Don't mind me, but how does the full query look like?

Posting Permissions

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