Say you need to get the last version before some point in time of a record from a DB that stores multiple versions of the same data timestamps, what would be the most efficient way?
After testing a few things we arrived on this little beauty, simple yet effective.
select * from tblMyTable a
WHERE not exists
( select 1 from tblMyTable a1
where a1.version > a.version and a1.id = a.id and a1.version <= @pointInTime )
and a.version <= @pointInTime )
A trap one might blunder into is not narrowing the outer query by @pointInTime which would give you too many records back