title: Oracle Joining with a subquery by maximum date
body: <p>I have a query as following below and this query has a subquery that gets the 
  maximum date with a windowing function. This subquery works like a charm without the rest of the query. But with the rest of query; it doesn't return the desired result. </p>
  
  <p>Problem is the result set has more than one rows (row count of CALLEXSERVICESALE times) with the same CALLID.  </p>
  
  <p>Query;</p>
  
  <pre><code>SELECT DISTINCT EC.*, CESS.AUDIT_CREATE_DATE,
      LCESS.NAME
  FROM ESASCASE EC
  LEFT JOIN CALLMAIN CM
      ON (
              EC.CALLID = CM.CALLID
              OR EC.CALLID = - 1
              )
  LEFT JOIN CALLDETAIL CD
      ON CM.CALLID = CD.CALLID
  LEFT JOIN LOVCASETYPE LCT
      ON EC.LOVCASETYPEID = LCT.LOVCASETYPEID
  LEFT JOIN LOVCASESTATUS LCS
      ON EC.LOVCASESTATUSCODE = LCS.LOVCASESTATUSCODE
  INNER JOIN UNIT U
      ON EC.UNITID = U.UNITID
  INNER JOIN UNIT U2
      ON U.PARENTUNITID = U2.UNITID
  LEFT JOIN CALLCOURIER CC
      ON EC.REFERENCEID = CC.CALLCOURIERID
  LEFT JOIN LOVPICKUPFAILUREREASON CNCL
      ON CC.LOVPICKUPFAILUREREASONID = CNCL.LOVPICKUPFAILUREREASONID
  LEFT JOIN LOVPICKUPFAILUREREASON DLAY
      ON CC.LOVPICKUPFAILUREREASONID = DLAY.LOVPICKUPFAILUREREASONID
  LEFT JOIN CALLEXSERVICESALE CESS
      ON CESS.CALLDETAILID = CD.CALLDETAILID
  LEFT JOIN LOVCALLEXSALESTATUS LCESS
      ON LCESS.LOVCALLEXSALESTATUSID = CESS.LOVCALLEXSALESTATUSID
  LEFT JOIN (
      SELECT d.CALLID,
          d.CALLDETAILID,
          d.AUDIT_CREATE_DATE
      FROM (
          SELECT CD.CALLID,
              CD.CALLDETAILID,
              CESS.AUDIT_CREATE_DATE,
              row_number() OVER (
                  PARTITION BY CD.CALLID ORDER BY CESS.AUDIT_CREATE_DATE DESC
                  ) seq
          FROM CALLDETAIL CD
          INNER JOIN CALLEXSERVICESALE CESS
              ON CD.CALLDETAILID = CESS.CALLDETAILID
          ) d
      WHERE d.seq = 1
      ) res
      ON CESS.CALLDETAILID = res.CALLDETAILID
          AND res.AUDIT_CREATE_DATE = CESS.AUDIT_CREATE_DATE
  LEFT JOIN WAYBILL W
      ON W.WAYBILLID = CC.WAYBILLID
  WHERE CM.AUDIT_DELETED = 0
      AND EC.LOVCASETYPEID = '7'
      AND LCS.LOVCASETYPEID = '7'
      AND EC.STARTDATE &gt;= :pDATE1
      AND EC.STARTDATE &lt;= :pDATE2
      AND EC.AUDIT_DELETED = 0
      AND (
          :pUNITID IS NULL
          OR U.UNITID = :pUNITID
          )
      AND (
          :pPARENTUNITID IS NULL
          OR U2.UNITID = :pPARENTUNITID
          )
      AND (
          :pCUSTOMERTYPE IS NULL
          OR DECODE(CC.ACCOUNTADDRESSID, '', 'x', 'y') = :pCUSTOMERTYPE
          )
  </code></pre>
  
  <p>Current Result Set;</p>
  
  <pre><code>EC.*                   CESS.AUDIT_CREATE_DATE    LCESS.NAME
  
  EC.* (same data)       20/10/2015 15:08:50       SALE CANCELLED
  EC.* (same data)       20/10/2015 09:12:30       SALE COMPLETED
  </code></pre>
  
  <p>Desired Result Set;</p>
  
  <pre><code>EC.*        CESS.AUDIT_CREATE_DATE    LCESS.NAME
  
  EC.*        20/10/2015 15:08:50       SALE CANCELLED
  </code></pre>
  
  <p>where the audit_create_date is latest.</p>