FIRST_VALUE AND LAST_VALUE SQL Server 2012 Feature

FIRST_VALUE

This function, as its name suggests, returns the first value from an ordered set of

values. This is really useful as we can calculate, inside a result set, the difference
between an initial value and each subsequent value on-the-fly. With no variables
and no re-querying, everything is done inside a single piece of Transact SQL:

SELECT SalesYear,

SalesAmount,
FIRST_VALUE (SalesAmount)
OVER (ORDER BY SalesAmount) AS FirstYearSales,
SalesAmount - FIRST_VALUE (SalesAmount)
OVER (ORDER BY SalesAmount) AS SalesGrowthSinceFirstYear
FROM Sales
ORDER BY SalesYear

Running this query will return the following result set:

SalesYear	SalesAmount	FirstYearSales	Grwoth from last year
2000	100	100	0
2001	150	100	50
2002	200	100	100
2003	250	100	150

Same way things will work for LAST_VALUE