How to use the Analytical Functions FIRST_VALUE and LAST_VALUE in SQL Server

SQL Server Analytical or Window Functions FIRST_VALUE and LAST_VALUE

The FIRST_VALUE , LAST_VALUE are the two Analytical functions in the SQL Server. These are the Window functions used to return the first value, last value in an ordered set of values.

FIRST_VALUE () :

The FIRST_VALUE function returns the first value in an ordered set of values. Return type of this function is same type as scalar_expression.

Syntax:

FIRST_VALUE( )

OVER ( [ partition_by_clause ] order_by_clause )

Examples:

Suppose we have a sample of orders with 20 records with order details as follows :

Select 

Order_Id, Order_date, Prod_Id, Units_Sold

From tbl_Sample

Scenario 1 :

Returning First_Value of the Order_Date and Order_Id based on Order By Prod_Id , without any Partition.

Select 

Order_Id, Order_Date, Prod_Id, Units_Sold,

FIRST_VALUE(Order_Date) Over ( Order by Prod_Id) as First_Order_Date,

FIRST_VALUE(Order_Id) Over ( Order by Prod_Id) as First_Order_Id

From tbl_Sample

# Result :

The first value of Order_Id , Order_Date will be return for each row. Please note that, if the first value is NULL, then the result also null.

Also note that, this will return First value (not a minimum value) from Order_Id, Order_Date columns after soring the Prod_Id column in order by clause.

Scenario 2 :
Returning First_Value of the Order_Date and Order_Id based on the Partition by Prod_Id and Order By Order_Date.

Select

Order_Id, Order_Date, Prod_Id, Units_Sold,

FIRST_VALUE(Order_Date) Over ( Partition By Prod_Id Order by Order_Date) as First_Order_Date,

FIRST_VALUE(Order_Id) Over ( Partition By Prod_Id Order by Order_Date) as First_Order_Id

From tbl_Sample S1

# Result :

Notes:

The FIRST_VALUE function returned the first values from Order_Date, Order_Id Columns for each Partition Window, based on the column Prod_Id.

If we observe output, since the first value for First_Order_Date is 'NULL' in a Partition Window, Prod_Id = 1234, hence the result is written as the 'NULL. It is same case for Prod_Id=1237.

LAST_VALUE () :

The LAST_VALUE function returns the last value in an ordered set of values. Return type of this function is same type as scalar_expression.

While using this Function with Partition by clause, we need to use a another clause " ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING " to return the last value, otherwise it returns the CURRENT ROW value.

Once the rows in a Partition are arranged according to the Order By clause, UNBOUNDED PRECEDING means from the beginning (first row) of the partition, and the UNBOUNDED FOLLOWING means up to the end (last row) of the partition.

Syntax:

FIRST_VALUE( )

OVER ( [ partition_by_clause ] order_by_clause )

Examples:

Suppose we have a sample of orders with 20 records with order details as follows.

Select 

Order_Id, Order_date, Prod_Id, Units_Sold

From tbl_Sample

Scenario 1 :

Returning LAST_Value of the Order_Date and Order_Id based on Order By Prod_Id , without any Partition.

Select 

Order_Id, Order_Date, Prod_Id, Units_Sold,

LAST_VALUE(Order_Date) Over ( Order by Prod_Id) as Last_Order_Date,

LAST_VALUE(Order_Id) Over ( Order by Prod_Id) as Last_Order_Id

From tbl_Sample

# Result :

Notes:

After sorting the Prod_Id as per Order by Clause, the last value in Order_Date for the Prod_Id 1234 is 'NULL' so that it returned 'NULL' as last value for those group of rows.

The LAST_VALUE function returned the last values from Order_Date, Order_Id columns for each Partition Window, based on the column Prod_Id, though we did not use the Partition by clause, it done automatically.

This may not be the case all the time. Suppose if we use as a Unique column like Order_Id in the Order by clause then it will return the Current Row, same as original Order_Id column.

Scenario 2 :

Returning LAST_Value of the Order_Date and Order_Id based on Partition by Prod_Id and Order By Order_Date.

Select

Order_Id, Order_Date, Prod_Id, Units_Sold,

LAST_VALUE(Order_Date) Over ( Partition By Prod_Id Order by Order_Date

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as Last_Order_Date,

LAST_VALUE(Order_Id) Over ( Partition By Prod_Id Order by Order_Date

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as Last_Order_Id

From tbl_Sample

Notes:

While using this Function with Partition by clause, we need to use a another clause " ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING " to return the last value, otherwise it returns the CURRENT ROW value.

Once the rows in a Partition are arranged according to the Order By clause, UNBOUNDED PRECEDING means from the beginning (first row) of the partition, and the UNBOUNDED FOLLOWING means up to the end (last row) of the partition.

# Result :

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

--------------------------------------------------------------------------------------------------------