SQL Server Portal

In SQL server 2012, Microsoft has introduced some new analytical functions and one of the most important functions in it is FIRST_VALUE. As It name implies, it does the same.

Lets discuss this function syntax, purpose and examples in detail.

Syntax :

    FIRST_VALUE ( [scalar_expression )
    OVER ( [ partition_by_clause ] order_by_clause [ rows_range_clause ] )

Purpose :
It returns the first value from a list in a specified order (By default ascending order). Also we can do the partition in it but it is optional.

Lets create a student table and insert few records in it to demonstrate FIRST_VALUE.

Create Table [Student]
(
[Student ID] int,
[Student Name] varchar(50),
[Subject] varchar(50),
[Marks] int
)
GO

Insert into [Student] values (3,'Derek','English',100)
Insert into [Student] values (3,'Derek','Math',60)
Insert into [Student] values (3,'Derek','Science',90)

Insert into [Student] values (1,'Bob','English',80)
Insert into [Student] values (1,'Bob','Math',75)
Insert into [Student] values (1,'Bob','Science',60)
 
Insert into [Student] values (2,'Sandra','English',70)
Insert into [Student] values (2,'Sandra','Math',80)
Insert into [Student] values (2,'Sandra','Science',40)

Select * from [Student] Order By [Student ID]

Example-1 :
In this example, FIRST_VALUE returns the First Name of the student in the class (In ascending order) .

Select
 [Student ID]
,[Student Name]
,[Subject]
,[Marks]
,FIRST_Value([Student Name]) Over (Order By [Student Name]) as [First Value]
from dbo.[Student]
--Result

Example-2 :
In this example, FIRST_VALUE returns the student name having high score in the class.

Select
 [Student ID]
,[Student Name]
,[Subject]
,[Marks]
,FIRST_Value([Student Name]) Over (Order By [Marks] Desc) as [First Value]
from dbo.[Student]
--Result

Example-3 :
In this example, FIRST_VALUE returns the student name having high score in each subject in the class.

Select
 [Student ID]
,[Student Name]
,[Subject]
,[Marks]
,FIRST_Value([Student Name]) Over (Partition By [Subject] Order By [Marks] Desc) as [First Value]
from dbo.[Student]
--Result

Reference : MSDN