First_Value and Last_Value

Overview

SQL Server 2012 introduces following analytical functions

• CUM_DIST & Percentile_DIST
• FIRST_Value & Last_Value
• LAG & LEAD
• Percent_Count & Percent_Range

First_Value & Last_Value functions allow you to get the same value for the first and last row for all records in a result set, it will also be useful in a scenario where you want to compare particular thing with max and minimum value of the same thing.

Syntax

FIRST_VALUE ( [scalar_expression ] ) 

    OVER ( [ partition_by_clause ] order_by_clause [ rows_range_clause ] )

Scenarios & Example

• Find out highest and lowest value for particular resultset

Let's say we have a table called "Marks" which contain the marks of each student, each subject. and we want to compare marks of particular subject for each student with highest and lowest marks then this is how we can get it,

SELECT StudentName, Marks, FIRST_VALUE (Marks) OVER (ORDER BY Marks) LowestMarks

, LAST_VALUE (Marks) OVER (ORDER BY Marks) HighestMarksSoFar

, LAST_VALUE (Marks) OVER (ORDER BY Marks ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) HighestMarks

FROM   Marks

WHERE  SubjectName = 'Maths'

StudentName	Marks	LowestMarks	HighestMarksSoFar	HighestMarks
Rob	67	67	67	100
Ryan	97	67	97	100
Bob	98	67	98	100
John	100	67	100	100

We can see that, when we use "Rows Between Unbound Preceding and Unbound Following" in LAST_Value function, then we are getting actual Highest value from that result-set otherwise it gives the highest value whatever we have got so far. This is NOT the case for First_Value function

• Find out highest and lowest value using Partition By clause

Let's say we want to get above result for all the subjects then in this case, we need to use Partition By clause.

SELECT SubjectName, StudentName, Marks, FIRST_VALUE (Marks) OVER (PARTITION BY SubjectName ORDER BY Marks) LowestMarks

, LAST_VALUE (Marks) OVER (PARTITION BY SubjectName ORDER BY Marks ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) HighestMarks

FROM   Marks

order by 1,3

SubjectName	StudentName	Marks	LowestMarks	HighestMarks
English	Ryan	89	89	99
English	Bob	93	89	99
English	John	94	89	99
English	Rob	99	89	99
Maths	Rob	67	67	100
Maths	Ryan	97	67	100
Maths	Bob	98	67	100
Maths	John	100	67	100
Science	Rob	87	87	98
Science	Ryan	94	87	98
Science	John	98	87	98
Science	Bob	98	87	98