Return closest timestamp from Table B based on timestamp from Table A with matching Product IDs

Goal: Create a query to pull the closest cycle count event (Table C) for a product ID based on the inventory adjustments results sourced from another table (Table A).

All records from Table A will be used, but is not guaranteed to have a match in Table C.

The ID column will be present in both tables, but is not unique in either, so that pair of IDs and Timestamps together are needed for each table.

Current simplified SQL

SELECT
    A.WHENOCCURRED,
    A.LPID,
    A.ITEM,
    A.ADJQTY,
    C.WHENOCCURRED,
    C.LPID,
    C.LOCATION,
    C.ITEM,
    C.QUANTITY,
    C.ENTQUANTITY
FROM
    A
LEFT JOIN
    C
ON A.LPID = C.LPID     
WHERE
     A.facility = 'FACID'
     AND A.WHENOCCURRED > '23-DEC-22'
     AND A.ADJREASONABBREV = 'CYCLE COUNTS'
ORDER BY A.WHENOCCURRED DESC
;

This is currently pulling the first hit on C.WHENOCCURRED on the LPID matches. Want to see if there is a simpler JOIN solution before going in a direction that creates 2 temp tables based on WHENOCCURRED.

I have a functioning INDEX(MATCH(MIN()) solution in Excel but that requires exporting a couple system reports first and is extremely slow with X,XXX row tables.

>Solution :

If you are using Oracle 12 or later, you can use a LATERAL join and FETCH FIRST ROW ONLY:

SELECT A.WHENOCCURRED,
       A.LPID,
       A.ITEM,
       A.ADJQTY,
       C.WHENOCCURRED,
       C.LPID,
       C.LOCATION,
       C.ITEM,
       C.QUANTITY,
       C.ENTQUANTITY
FROM   A
       LEFT OUTER JOIN LATERAL (
         SELECT *
         FROM   C
         WHERE  A.LPID = C.LPID
         AND    A.whenoccurred <= c.whenoccurred
         ORDER BY c.whenoccurred
         FETCH FIRST ROW ONLY
       ) C
       ON (1 = 1) -- The join condition is inside the lateral join
WHERE   A.facility = 'FACID'
AND     A.WHENOCCURRED > DATE '2022-12-23'
AND     A.ADJREASONABBREV = 'CYCLE COUNTS'
ORDER BY A.WHENOCCURRED DESC;