New Analytic Functions in SQL Server 2012

Analytic Functions

First_Value Function
It returns the first value in ordered set of values.

Syntax:
First_Value([scalar_expression])
OVER([partition_by_clause] order_by_clause[rows_range_clause])

•Scalar_expression: can be a column, sub query, 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 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 group or a single partition and then ranking functions are applied.

Example:
We create a table named student
Create table student
(
Sid int,
Sname varchar(30),
Marks int
)
Insert the values into table
Insert into student values(1,akil,320)
Insert into student values(1,balu,520)
Insert into student values(1,chini,580)
Insert into student values(1,devid,570)
Insert into student values(1,etil,471)
Assume the following query:
Select *,first_value(Marks) OVER (order by sid) as First_valueResult from student

Output:

Last_Value Function
It returns the last value in ordered set of values in database.

Syntax:
Last_Value([scalar_expression])
OVER([partition_by_clause] order_by_clause[rows_range_clause])

•Scalar_expression: can be a column, sub query, 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 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 group or a single partition and then ranking functions are applied.

Example:
Select *,last_value(Marks) OVER (order by sid) as First_valueResult from student

Output: