FIRST_VALUE() and LAST_VALUE()

interview preparation

Difference Between FIRST_VALUE() and LAST_VALUE() in SQL

Both FIRST_VALUE() and LAST_VALUE() are analytics window functions that return specific values within a partition or window. The difference lies in which value they return: the first value or the last value based on the window’s ordering.

1. FIRST_VALUE():

• Purpose: Returns the first value in the window (or partition) based on the ordering of rows within that partition.
• Usage: It fetches the value from the first row in the window (partition) when sorted by a specified column in ascending or descending order.

Example: If we want to find the first sales amount for each salesperson, regardless of the current row’s position:

SELECT 
    SaleDate, 
    SalesAmount, 
    SalesRep,
    FIRST_VALUE(SalesAmount) OVER (PARTITION BY SalesRep ORDER BY SaleDate) AS FirstSaleAmount
FROM Sales;

In this example, FIRST_VALUE(SalesAmount) always returns the first sales amount for each SalesRep based on the SaleDate.

2. LAST_VALUE():

• Purpose: Returns the last value in the window (or partition) based on the ordering of rows within that partition.
• Usage: It fetches the value from the last row in the window, which can be useful when calculating the most recent value in a sequence.

Example: If we want to find the last sales amount for each salesperson, regardless of the current row’s position:

SELECT 
    SaleDate, 
    SalesAmount, 
    SalesRep,
    LAST_VALUE(SalesAmount) OVER (PARTITION BY SalesRep ORDER BY SaleDate ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LastSaleAmount
FROM Sales;

Here, LAST_VALUE(SalesAmount) returns the last sales amount for each SalesRep, which is 400 for Alice and 500 for Bob.

Key Differences

Important Consideration

• For LAST_VALUE() to work correctly across the entire window, the window frame should be defined as ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, otherwise, it might not return the true last value (it might just return the current row’s value).

Example with correct window frame for LAST_VALUE():

SELECT 
    SaleDate, 
    SalesAmount, 
    SalesRep,
    LAST_VALUE(SalesAmount) OVER (PARTITION BY SalesRep ORDER BY SaleDate ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LastSaleAmount
FROM Sales;