I got this table:

What I want is a table that gives the CLOSEST events BEFORE "INJECTION" per eye:

Desired output (Ignored rows 4-5):

What I've tried is:

WITH T
AS
(
SELECT
     ID,
     DATE,
     EVENT,
     EYE,
     ROW_NUMBER() OVER (PARTITION BY ID,DATE ORDER BY DATE DESC) 
     ELSE NULL END
     AS RN
FROM 
    MY_TABLE
)
SELECT*
FROM T
WHERE RN < 4

That works partially - for this specific occasion but not for several ID's.

CodePudding user response：

I believe CROSS APPLY or OUTER APPLY is what you need. Both join to a subselect which allows you to select specific matching criteria, ordering, and most importantly in this case TOP 3.

A CROSS APPLY is like an (inner) JOIN', while OUTER APPLYis like aLEFT JOIN`. Using the latter allows for cases where an injection has no prior event.

The resulting query would be something like:

SELECT PRIOR.*
FROM MY_TABLE T
OUTER APPLY (
    SELECT TOP 3 *
    FROM MY_TABLE T2
    WHERE T2.ID = T.ID
    AND T2.EYE = T.EYE
    AND T2.DATE <= T.DATE  -- Should we allow same-day event?
    ORDER BY T2.DATE DESC
) PRIOR
WHERE T.EVENT = 'INJECTION'
ORDER BY T.ID, T.DATE, T.EYE, PRIOR.DATE

Results:

See this db<>fiddle for a demo.

There may still be some ambiguity as to what is considered "before" for same-day events. You might want to look closer at that.

(The above was edited to change TOP 1 to TOP 3 after further review of your question.)