ILearnable .Net

December 7, 2005

Christmas Calendar 7/24

Filed under: Uncategorized — andreakn @ 01:29

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

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: