title: Select rows with latest timestamp using certain criteria and additional tables SQL
body: <p>I was hoping someone could help me in resolving this issue. </p>
  
  <p>I have 3 tables.</p>
  
  <p>Every table has an ID that I can use to connect all of them together. Those ID's are not the same ones, but they work and connect to each other alright. </p>
  
  <p>What I need to get is LATEST update date (timestamp) for certain product number (could be more than one product) what was status for it. </p>
  
  <p>Here are how tables look like (they are much more complex, but i simplified them)</p>
  
  <pre><code>mercury.cs_batch_event
  PRODUCT_ID (VARCHAR2(100 CHAR))
  CBL_REQUEST_ID (VARCHAR2(12 CHAR))
  
  mercury.cs_status_srch
  CBL_REQUEST_ID (VARCHAR2(12 CHAR))
  LOG_ID  (VARCHAR2(12 CHAR))
  STATUS_REF_ID (NUMBER(8,0))
  
  mercury.edit_log
  ELOG_ID (VARCHAR2(12 CHAR))
  ON_DATE_TIME (TIMESTAMP(6))
  </code></pre>
  
  <p>I tried doing it several different ways, and after searching for a while on the internet, closest i could get to was this:</p>
  
  <pre><code>SELECT * 
  FROM (SELECT cbl.product_id, src.status_ref_id,
               TO_CHAR(elog.on_date_time, 'dd/mm/yyyy hh24:mi:ss') date_updated
        FROM mercury.cs_batch_event cbl,
             mercury.cs_status_srch src, 
             mercury.edit_log elog
        WHERE cbl.product_id IN ('A555', 'B555')
          AND cbl.cbl_request_id = src.cbl_request_id
          AND src.log_id = elog.elog_id
        ORDER BY elog.on_date_time DESC)
  WHERE rownum = 1
  </code></pre>
  
  <p>And it returned below which is correct, but when several product numbers are inserted it still returns value for only one product (Which is because of the <code>row number=1</code> probably)</p>
  
  <pre><code>PRODUCT_NUMBER  DATE_UPDATED        STATUS
  A555            29/06/2011 07:51:24 30169
  </code></pre>
  
  <p>Thanks</p>