Saturday, March 08, 2014

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

No comments:

Post a Comment