Need To Retrieve Table's Last Inserted/updated Record With Some Exclusions

I am trying to retrieve the last record inserted/updated in the table SHIPMENT_EVENT. There are a couple of these events/codes that are not needed for instance all beginning with '

Solution 1:

Your problem is that you're filtering out undesired events after getting the most recent row:

WHERERN=1  and ship_evnt_cd not like('9%')

You need to filter first, and get the most-recent row from those that are left:

LEFTJOIN (SELECT ship_id, ship_to_loc_cd856, ship_evnt_cd, 
                  updt_job_tms, carr_tracking_num, event_srv_lvl, 
                  ROW_NUMBER() OVER(PARTITIONBY ship_id ORDERBY updt_job_tms DESC) AS rn
           FROM Shipment_Event
           -- This _might_ perform better than the NOT LIKE condition, you'll need to testWHERELEFT(ship_evnt_cd, 1) <>'9') lscus
       ON lscus.ship_id = scus.ship_id 
          AND lscus.ship_to_loc_cd856 = scus.ship_to_loc_code 
          AND lscus.rn =1

Solution 2:

If you have some ship_ids that only have 9% events, then you would need, e.g.

select  ship_id, ship_to_loc_cd856, ship_evnt_cd
,     updt_job_tms, carr_tracking_num, event_srv_lvl
,     row_number() over(partitionby ship_id orderby updt_job_tms desc) as RN
FROM    shipment_event e
WHERE   ship_evnt_cd notlike'9%'ORNOTEXISTS (SELECT1from shipment_event s 
                    WHERE s.ship_id = e.ship_id 
                     AND ship_evnt_cd notlike'9%')

as your sub-select