Microsoft SQL Server Window functions Find the single most recent item in a list of timestamped events
Example
In tables recording events there is often a datetime field recording the time an event happened. Finding the single most recent event can be difficult because it's always possible that two events were recorded with exactly identical timestamps. You can use row_number() over (order by ...) to make sure all records are uniquely ranked, and select the top one (where my_ranking=1)
select *
from (
select
*,
row_number() over (order by crdate desc) as my_ranking
from sys.sysobjects
) g
where my_ranking=1
This same technique can be used to return a single row from any dataset with potentially duplicate values.
