Remember ROW_NUMBER, RANK & DENSE_RANK functions in SQL Server 2005 as well as in SQL Server 2008? LAST_VALUE & FIRST_VALUE function shares the similar syntax. After seeing all these functions, I remember those hard days of SQL Server 2000 when we had to write script for this kind of need. Really!!! Technology make us so productive ( and dumb too :) )
LAST_VALUE: As name suggests, this function will return the last value from the ordered set values.
FIRST_VALUE: FIRST_VALUE function is also behaving like its name; it returns the first value from the ordered set values.
Let us see how it actually works with following simple example which only works in SQL Server 2012.
[sourcecode language=”sql”]DECLARE @AppraisalHistory TABLE(
ID INT IDENTITY(1,1),
INSERT INTO @AppraisalHistory
SELECT ‘Ritesh’,DATEADD(YEAR,-2,GETDATE()),10000 UNION ALL
SELECT ‘Ritesh’,DATEADD(YEAR,-1,GETDATE()),12000 UNION ALL
SELECT ‘Ritesh’,GETDATE(),15000 UNION ALL
SELECT ‘Bihag’,DATEADD(YEAR,-2,GETDATE()),9000 UNION ALL
SELECT ‘Bihag’,DATEADD(YEAR,-1,GETDATE()),10000 UNION ALL
LAST_VALUE(TotalSalary) OVER (PARTITION BY NAME ORDER BY ID) AS LastSalary ,
FIRST_VALUE(TotalSalary) OVER (PARTITION BY NAME ORDER BY ID) AS FirstSalary
ENJOY new SQL Server with all its power!!!!
Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles.