Oracle query - get latest record if multiple records are returned

Question

I am using a stored procedure inside a package that connects to a DB using db link and periodically pulls data to update local DB. It does so by pulling data into a "staging" table, do some processing and update local table.

It worked fine until I ran into an issue where I ended up with multiple/duplicate records having different CREATEDON timestamps but same ITEM_ID and EVENTCODE.

This is a watered down version of the query:

LVSPICKSQL:='INSERT INTO STAGING_TABLE (A, B, ITEM_ID, EVENTCODE, CREATEDON)
SELECT (A, B, ITEM_ID, EVENTCODE, CREATEDON) FROM SOME.DBLINK@BLAH WHERE STATUS=1 AND PCIKEDON IS NULL) WHERE ROWNUM <= 200;

EXECUTE IMMIDIATE LVSPICKSQL;

I thought combination of ITEM_ID and EVENTCODE were unique; found out that is not the case.

Now, I need to modify this query so that if there are multiple records for same ITEM_ID and EVENTCODE, I pick the latest one (latest CREATEDON).

Answer 1

You can use window functions for that:

select A, B, ITEM_ID, EVENTCODE, CREATEDON from 
    (select A, B, ITEM_ID, EVENTCODE, CREATEDON, 
                   row_number() over(partition by ITEM_ID, EVENTCODE 
                        order by CREATEDON desc) rn from SOME.DBLINK@BLAH
     where STATUS=1 and PCIKEDON is null
    )
where rn = 1;