I'm trying to get a query that finds a specific item in a sequence of events which occurs just before an event another table.
My query looks like this:
from Events1 x
left join Events2 y on x.sharedkey = y.sharedkey and y.EventKey = (select top 1 z.eventkey from Events2 z where z.sharedkey = x.sharedkey and z.startdate < x.startdate order by z.startdate desc)
However, I was surprised to discover that with this syntax, SQL ignores the "where z.stardate < x.startdate" clause and simply returns the last event in table y, even if it occurs after the event in table x.
Does TSQL always do this? Is there a way around it to achieve the result I need?