Sql Query Question

I have the following Query that works, except for one thing:

SELECT DISTINCT
     r.ssps_wrkr_id,
     wr.ssps_wrkr_asgn_strt_date,
     --WR.SSPS_WRKR_ID,
      --MAX(WR.SSPS_WRKR_ASGN_STRT_DATE) AS START_DATE,
     R.DCFREF_ID,
     R.DCFREF_RECV_DATE,
     R.cpsra_num_code as risk_tag,
     x.PRICAR,
     CASE WHEN r.admin_id IS NOT NULL THEN 'ADMIN_RECORD' ELSE x.PRICAR_PRSN_NAME END "PRICAR_PRSN_NAME",
     y.VICTIM,
      CASE WHEN r.admin_id IS NOT NULL THEN 'ADMIN_RECORD' ELSE y.VICTIM_NAME END "VICTIM_NAME",
     S.ADABAS_ISN as "SER_ID",
     CASE WHEN SA.ACTN_CODE LIKE 'AI' AND S.ACTN_DATE_9S_NUM IS NOT NULL THEN S.ACTN_DATE_9S_NUM END "ATT_INIT_DATE",
  --   CASE WHEN SA.ACTN_CODE LIKE 'WA' AND S.ACTN_DATE_9S_NUM IS NOT NULL THEN S.ACTN_DATE_9S_NUM END "WAIVER_DATE",
     CASE WHEN SA.ACTN_CODE LIKE 'IF' AND S.ACTN_DATE_9S_NUM IS NOT NULL THEN S.ACTN_DATE_9S_NUM END "INIT_F2F_DATE",
     CASE WHEN SA.ACTN_CODE LIKE 'FC' THEN S.ACTN_DATE_9S_NUM END "FACE_2_FACE_DATE",
    Z.SAFTEY_DATE,
    K.INVESTIGATIVE_DATE
FROM WORKING.DCFREF R,
      (SELECT  a.DCFREF_ID,
                  b.PRSN_ID as PRICAR,
                  (b.PRSN_LAST_NAME || ', ' || b.PRSN_1ST_NAME) as PRICAR_PRSN_NAME
       FROM       WORKING.DCFREF_PRSN a,
                  WORKING.PRSN b
       WHERE       a.PRSN_ID = b.PRSN_ID
       AND upper(a.prsn_pricar_flg) like 'Y') x,
     (SELECT       a.DCFREF_ID,
                      b.PRSN_ID as VICTIM,
                     (b.PRSN_LAST_NAME || ', ' || b.PRSN_1ST_NAME) as VICTIM_NAME
          FROM WORKING.DCFREF_PRSN a,
               WORKING.PRSN b
         WHERE a.PRSN_ID=b.PRSN_ID
           AND a.ref_victim_flg like 'Y') y,
     (SELECT  D.DCFREF_ID,
                T.ASSESS_ID,
                T.REC_TYPE_CODE,
                T.ASSESS_CMPLT_DATE AS SAFTEY_DATE
           FROM       WORKING.assessment T,
                WORKING.ASSESSMENT_DCFREF_RLSHP D
         WHERE D.ASSESS_ID = T.ASSESS_ID
         AND      T.rec_type_code like 'A1') Z,
  (SELECT  E.DCFREF_ID,
           U.ASSESS_ID,
           U.REC_TYPE_CODE,
           U.ASSESS_CMPLT_DATE AS INVESTIGATIVE_DATE
            FROM       WORKING.assessment U,
              WORKING.ASSESSMENT_DCFREF_RLSHP E
            WHERE U.ASSESS_ID = E.ASSESS_ID
        AND  U.rec_type_code like 'A4') K,
working.SER S,
working.SER_PRSN_ID SP,
working.ser_actn_code SA,
working.wrkr_rlshp WR,
WORKING.SER_DCFREF_ID SD
 WHERE R.DCFREF_ID=x.DCFREF_ID
AND R.DCFREF_ID=y.DCFREF_ID
AND R.DCFREF_ID = SD.DCFREF_ID
AND S.ADABAS_ISN = SD.ADABAS_ISN
AND SD.adabas_isn = SP.adabas_isn
AND SD.adabas_isn = SA.adabas_isn
AND SA.ACTN_CODE IN ( 'IF', 'AI', 'WA', 'FC')
AND R.DCFREF_ID = 1462757
AND Y.VICTIM = SP.PRSN_ID
AND WR.ssps_wrkr_role_code like 'AW'
AND K.DCFREF_ID = R.DCFREF_ID
AND Z.DCFREF_ID = R.DCFREF_ID
AND R.DCFREF_ID = WR.FOREIGN_KEY_NUM


The query returns more than one row because there may be more than one worker (assigned to the dcfref_id) with the ssps_role_code of 'AW'.  What I want to do is pull back the one with the latest ssps_wrkr_asgn_strt_date.  Any ideas of how to incorporate that into this query?

Thanks,

Start Free Trial