Wednesday, December 07, 2005

Christmas Calendar 7/24

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 = 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

No comments: