🥇 Peeking Into the Window: FIRST_VALUE(), LAST_VALUE(), NTH_VALUE()

🚀 Peeking Behind the Rows: Why These Functions Matter

"Xorthax doesn’t just want totals—he’s nosy. He wants to know what happened first, what came last, and what the third-highest bid was. These functions let you peek at specific rows in the window frame without collapsing data."

🤔 What Are FIRST_VALUE(), LAST_VALUE(), and NTH_VALUE()?

These functions let you return a specific value from within the window frame for each row.

• FIRST_VALUE() returns the first value in the window frame
• LAST_VALUE() returns the last value in the window frame
• NTH_VALUE(n) returns the nth value in the frame (you specify n)

They’re great for comparisons, flags, or “difference from benchmark” logic.

🔧 Syntax Snapshot

FIRST_VALUE(column) OVER (...)
LAST_VALUE(column) OVER (...)
NTH_VALUE(column, n) OVER (...)

Typically used with PARTITION BY and ORDER BY, and very often customized with a frame clause to control what “first” and “last” mean.

🪟 What Is a Frame Clause?

A frame clause defines which rows are included when a window function runs for the current row. It controls the “visible slice” of the partition.

Examples:

• ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — includes all rows up to the current one
• ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING — includes current row and all after it

This is critical for LAST_VALUE() and NTH_VALUE() — without it, they might return the current row’s value instead of the truly last or nth.

📋 See It in Action

🧾 Sample Data

🔎 Query Example

SELECT CustomerID, OrderDate, Amount,
       FIRST_VALUE(Amount) OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS FirstAmt,
       LAST_VALUE(Amount) OVER (PARTITION BY CustomerID ORDER BY OrderDate ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS LastAmt,
       NTH_VALUE(Amount, 2) OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS SecondAmt
FROM Orders;

📊 Result

🔍 Explanation:

• FIRST_VALUE is consistent for each row in the partition
• LAST_VALUE needs a frame clause, otherwise it returns the “last so far” (same as current row)
• NTH_VALUE returns NULL if there aren’t enough rows in the frame yet

🧭 When Should You Use Each?

• Use FIRST_VALUE() when you need the starting point (first price, first date, first event)
• Use LAST_VALUE() to compare against the final row’s value — just make sure to define the full frame!
• Use NTH_VALUE(n) to grab a benchmark row (like “third purchase” or “fifth attempt”)

🧠 Keywords That Hint You're in the Right Place

• “first ever” / "last ever"
• “most recent value”
• “the third [whatever]”
• “beginning to end”

⚠️ Common Pitfalls

• ❌ Forgetting the frame clause for LAST_VALUE() — you’ll just get the current row’s value
• ❌ Expecting NTH_VALUE() to always return a result — early rows won’t have enough data
• ❌ Assuming FIRST_VALUE() always means the earliest in time — it depends on ORDER BY
• ❌ These functions do not handle NULLs automatically like LAG() or LEAD(). You'll need to wrap them in COALESCE() or filter out NULLs manually.

🔄 CTEs and Difference Calculations

Compare each row to the first or last value in its partition.

WITH Labeled AS (
  SELECT *,
         FIRST_VALUE(Amount) OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS FirstAmt
  FROM Orders
)
SELECT CustomerID, OrderDate, Amount, FirstAmt,
       Amount - FirstAmt AS DiffFromFirst
FROM Labeled;

🔍 Explanation: This query uses a CTE to assign the first value in each partition to every row. Then, in the outer query, it subtracts that first amount from the current row’s amount to calculate the difference.

The CTE is necessary because you can't reference a window function directly in the same SELECT clause where you apply a transformation like subtraction.

➕ Combine with Aggregates

Use these value-based functions to set context before aggregating:

WITH Anchored AS (
  SELECT CustomerID, OrderDate, Amount,
         FIRST_VALUE(OrderDate) OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS StartDate
  FROM Orders
)
SELECT CustomerID,
       MIN(OrderDate) AS FirstOrder,
       MAX(OrderDate) AS LastOrder,
       COUNT(*) AS TotalOrders,
       MAX(OrderDate) - MIN(OrderDate) AS Duration
FROM Anchored
GROUP BY CustomerID;

🔍 Explanation: This query uses FIRST_VALUE() to anchor the starting date of a customer’s activity. Then it applies regular aggregate functions like MIN(), MAX(), and COUNT() to summarize the order history by customer. It shows how window functions can prepare contextual data before aggregation.

🧰 Debugging Tips

• Always check your frame! Especially with LAST_VALUE() and NTH_VALUE()
• Confirm the ORDER BY is sorting as you expect
• Use ROW_NUMBER() in test queries to verify sequence

🔗 Related Topics