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;| SaleDate | SalesAmount | SalesRep | FirstSaleAmount |
|---|---|---|---|
| 2024-01-01 | 100 | Alice | 100 |
| 2024-01-03 | 150 | Alice | 100 |
| 2024-01-05 | 400 | Alice | 100 |
| 2024-01-02 | 200 | Bob | 200 |
| 2024-01-04 | 300 | Bob | 200 |
| 2024-01-06 | 500 | Bob | 200 |
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;| SaleDate | SalesAmount | SalesRep | LastSaleAmount |
|---|---|---|---|
| 2024-01-01 | 100 | Alice | 400 |
| 2024-01-03 | 150 | Alice | 400 |
| 2024-01-05 | 400 | Alice | 400 |
| 2024-01-02 | 200 | Bob | 500 |
| 2024-01-04 | 300 | Bob | 500 |
| 2024-01-06 | 500 | Bob | 500 |
Here, LAST_VALUE(SalesAmount) returns the last sales amount for each SalesRep, which is 400 for Alice and 500 for Bob.
Key Differences
| Aspect | FIRST_VALUE() | LAST_VALUE() |
|---|---|---|
| Function | Returns the first value in the window or partition. | Returns the last value in the window or partition. |
| Window Behavior | Based on the first row as per ORDER BY within the window. | Based on the last row as per ORDER BY within the window. |
| Window Frame | Default window frame is from the first row to the current row. | Default window frame is from the current row to the last row. |
| Common Use Case | Used when you need the first entry in a group (e.g., the first sales amount in a day or period). | Used when you need the last entry in a group (e.g., the most recent sales or transaction). |
Important Consideration
- For
LAST_VALUE()to work correctly across the entire window, the window frame should be defined asROWS 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;