Looking forward to FIRST_VALUE and LAST_VALUE. I'm confident writing equivalent functionality using ROW_NUMBER() or TOP but our second tier app support guys (who sometimes venture into customer data) have trouble utilising the ranking functions, since ROW_NUMBER()/TOP ventures into subquery/CTE territory.