In this article, I would like to show the new analytic functions First_Value and Last_Value in SQL Server. First_Value and Last_Value are part of the analytic functions. The First_Value Function returns the first value in an ordered set of values, and Similarly Last_Value function returns the last value from an ordered set of values. So let's have a look at a practical example of how to use the First_Value and Last_Value functions in SQL Server 2012. The example is developed in SQL Server 2012 using SQL Server Management Studio.
We'll start by walking through a simple First_Value function.
Creating a table in SQL Server
Now we create a table named employee.
Create table Employee
(
EmpID int,
EmpName varchar(30),
EmpSalary int
)
The following is the sample data for the employee Table.
First_Value Function
The First_Value function is a new analytic function in SQL Server. It returns the first value in an ordered set of values. Here, you will see some examples related to the First_Value function.
Syntax
The following is the SQL Analytic First_Value function syntax:
First_Value ( [scalar_expression )
OVER ([partition_by_clause] order_by_clause)
Scalar_expression - can be a column, subquery, or other expression that results in a single value.
OVER - Specify the order of the rows.
ORDER BY - Provide sort order for the records.
Partition By - Partition by clause is a optional part of First_Value function and if you don't use it all the records of the result-set will be considered as a part of single record group or a single partition.
Example
Let us see the following query:
Select *, First_value(EmpSalary) OVER (order BY EmpSalary ) as First_ValueResut From Employee
Output
First_Value Function with Partition By Clause
The Partition by clause is an optional part of First_Value function. By using the PARTITION BY clause with FIRST_VALUE function we can divide the result set by name.
Example
Select *, Lead(EmpName) OVER (partition by EmpName ORDER BY EmpName DESC) AS Result From Employee
Output
Last_Value Function
The Last_Value function is also a new analytic function in SQL Server. It returns the last value in an ordered set of values. Here, you will see some examples related to the Last_Value function.
Syntax
The following is the SQL Analytic Last_Value function syntax:
Last_Value ( [scalar_expression )
OVER ([partition_by_clause] order_by_clause)
Scalar_expression - can be a column, subquery, or other expression that results in a single value.
OVER - Specify the order of the rows.
ORDER BY - Provide sort order for the records.
Partition By - Partition by clause is a optional part of Last_Value function and if you don't use it all the records of the result-set will be considered as a part of single record group or a single partition and then ranking functions are applied.
Example
Let us see the following query:
Select *, LAST_VALUE(EmpSalary) OVER(ORDER BY EmpSalary ) AS Last_Salary
FROM Employee
Output
Last_Value Function with Partition By Clause
The Partition by clause is an optional part of the Last_Value function. By using the PARTITION BY clause with the Last_Value function we can divide the result set by name.
Example
Select *, LAST_VALUE(EmpSalary) OVER(partition by EmpName ORDER BY EmpSalary ) AS Last_Salary
FROM Employee
Output