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 then 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
I started wondering if I could make the inner query return the ranking for only part of its result set.
What I wanted was a resultset that gave me all the records after a given date and a ranked list of those prior to it. 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
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.