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
[…] Comments « SQL SERVER 2012 – Analytic Function – FIRST_VALUE […]
Is there a second_value() function?
or how can I select second value by analytical ranking function(not ranking)?
Hi User,
There is no Second_value function in the sql server. But I will check the solution and update you shortly.
Thank you.
Imran
[…] We do have the solution using RANK(ROW_NUMBER) function. However, we can achieve it using FIRST_VALUE function (Shipped with SQL Server 2012) as […]
[…] We do have the solution using RANK(ROW_NUMBER) function. However, we can achieve it using FIRST_VALUE function (Shipped with SQL Server 2012) as […]