LAST_VALUE and FIRST_VALUE function in SQL Server 2012

LAST_VALUE and FIRST_VALUE function in SQL Server 2012

After providing introduction to the new Conversion functions and DATE TIME functions in SQL Server, I come up with analytical functions LAST_VALUE and FIRST_VALUE.

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.

DECLARE @AppraisalHistory TABLE(

ID INT IDENTITY(1,1),

Name VARCHAR(35),

AppDate DATE,

TotalSalary INT

)

 

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

SELECT 'Bihag',GETDATE(),16000

SELECT

*,

LAST_VALUE(TotalSalary) OVER (PARTITION BY NAME ORDER BY ID) AS LastSalary ,

FIRST_VALUE(TotalSalary) OVER (PARTITION BY NAME ORDER BY ID) AS FirstSalary

FROM @AppraisalHistory

ENJOY new SQL Server with all its power!!!!

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

6 thoughts on “LAST_VALUE and FIRST_VALUE function in SQL Server 2012

  1. Jack Owens

    Ritesh,

    This URL is very informational
    If you could also print the RESULT of your SQL Query, that will help reader like me to understand what the Query Result will look like & also SAVE my time to COPY the code & TRY the code on my local SQL Instance

    In short the web-page should explain the Query and Result right in single place :)

  2. Jack Owens

    Ritesh,

    When I select the sample SQL Code given this web-page it also copies the Line # which is very annoying
    Can you PASTE your SQL Code w/o the line #?

  3. Pingback: LEAD and LEG Analytic functions in SQL Server 2012

  4. Pingback: New Logical Function IIF in SQL Server 2012

Comments are closed.