Using Rank In Case in SQL Server 2005, 2008, 2012, 2014 etc.

Here’s a quick tip for a Friday.

I have a table that records the history of certain items. I wanted to get a list of the updates between two dates – that’s easy enough. The problem was that, for each item, I also wanted the last change before the start of the time period.

A standard way to get the latest update for each item in a history table is use a nested pair of queries, the inner one using the RANK function to provide an order. The outer query the selects all the records with a RANK of 1 as these are the latest (or the earliest, or maximum, or minimum, depending on the order specified).

e.g.

select * from (
select ml.*,rank() over(partition by itemID order by [timeColumn] desc) rk
from someHistoricTable ml ) T1
where rk=1

So I started thinking – I wonder if I can make the inner query return the ranking for only part of its result set – when the results were in the time period I was interested in then I didn’t need them ranked, when they were before I did. So I wondered if I could us the RANK function within a CASE statement.
Yes, apparently you can…

select * from (
select ml.*,
case
when [time] < @startTime
then rank() over(partition by [itemID] order by [timeColumn] desc)
else 1
end rk
from someHistoricTable ml
where [timeColumn] < @stopTime ) T1
where rk=1 order by [time] desc

So the case / rank bit splits the time, it ranks records that were earlier than the @startTime whereas if they’re later it just returns ‘1’. The outer select then just takes all rows that have an ‘rk’ of 1, these being the records that are either during the time period or the highest ranked row before.