FIRST_VALUE vs LAST_VALUE in SQL

FIRST_VALUE vs LAST_VALUE in SQL (Avoid This Common Window Function Trap)

After RANK, DENSE_RANK, ROW_NUMBER, LEAD, and LAG, the next underrated but powerful SQL window functions are
👉 FIRST_VALUE() and LAST_VALUE().

They look simple—but many people get wrong results in interviews and real projects due to misunderstanding window frames.

Let’s clear that confusion once and for all.

🧠 Problem Statement

👉 From a sales table, find:

• First sale amount
• Latest sale amount
  for each product.

Sample data:

🥇 Using FIRST_VALUE()

SELECT product,
       sale_date,
       amount,
       FIRST_VALUE(amount) OVER (
           PARTITION BY product
           ORDER BY sale_date
       ) AS first_sale
FROM sales;

✔ What it does

• Returns the first value in the ordered window
• Works as expected without extra configuration

Result for product A:

• First sale = 100 for all rows

⚠️ The Trap with LAST_VALUE()

SELECT product,
       sale_date,
       amount,
       LAST_VALUE(amount) OVER (
           PARTITION BY product
           ORDER BY sale_date
       ) AS last_sale
FROM sales;

❌ Surprise result!

Instead of returning 200, this query returns the current row’s amount.

Why?

👉 Because by default, SQL considers the window frame as:

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

So LAST_VALUE() stops at the current row, not the actual last row.

✅ Correct Way to Use LAST_VALUE()

SELECT product,
       sale_date,
       amount,
       LAST_VALUE(amount) OVER (
           PARTITION BY product
           ORDER BY sale_date
           ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS last_sale
FROM sales;

✔ Now it works correctly:

• Last sale = 200 for all rows of product A

📌 Key Difference (Interview Favorite)

👉 Always define the window frame when using LAST_VALUE().

💡 Real-World Use Cases

• First vs latest transaction
• Opening vs closing balance
• First login vs last login
• Initial vs final price comparison

💬 Interview Question for You

How would you calculate the price difference between first and last transaction per product using window functions only?

(Hint: Combine FIRST_VALUE() and LAST_VALUE() 😉)