Hi - in my application we have several historical tables that have a column such as "AsOfDate" and store snapshots of the data on certain dates. We then have many queries where we need to query what the data looked like as of certain points in time. I know there are several ways to do this, but was wondering if anyone had a more clever way. The most common way we do it is something like this: select * from MyTable t inner join ( select SomeID, MAX(AsOfDate) from MyTable t2 where AsOfDate <= @AsOfDate) on t.SomeID = t2.SomeID and t.AsOfDate = t2.AsOfDate Ideally, I'm looking for a way to do with without having to do some sort of sub-query as above - for performance and readability reasons. It would be nice if I could do something like below, but SQL Server doesn't allow it: select * from MyTable where ROW_NUMBER() over (partition by SomeID, AsOfDate order by AsOfDate desc) = 1 I know you can also use a CTE or a derived table, but again, looking for a clever way to do it "on the fly". Anyone have any ideas?