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 differencebetween 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