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:
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: