🥇 Peeking Into the Window: SQL FIRST VALUE, LAST VALUE, and NTH_VALUE Functions

This tutorial explains how to use SQL window functions like FIRST_VALUE(), LAST_VALUE(), and NTH_VALUE() — also known as the SQL first value, last value, and nth value functions. You’ll learn how to return specific values from a window frame to compare each row with the first, last, or nth row in its group.

🚀 Why SQL FIRST VALUE, LAST VALUE and NTH VALUE 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 in SQL?

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: SQL FIRST_VALUE, LAST_VALUE and NTH_VALUE Examples

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 in and how does it work with FIRST VALUE, LAST VALUE and NTH VALUE?

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.

📋 FIRST_VALUE, LAST_VALUE and NTH_VALUE - 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 FIRST_VALUE vs LAST_VALUE vs NTH_VALUE?

• 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 FIRST VALUE, LAST VALUE and NTH VALUE 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.

🔄 Using Difference Calculations - FIRST_VALUE example

Sometimes you want to compare each row’s value to the FIRST_VALUE() in its partition — for example, to measure growth or difference from a starting point.

🧪 Sample Data

🪟 Query Example

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

📊 Result

🔍 Explanation: The FIRST_VALUE() function assigns the earliest amount for each CustomerID to every row in that group. Subtracting it from the current amount gives a simple measure of change since the first order.

💡 You can apply the same idea with LAST_VALUE() to compare against the final amount, or with NTH_VALUE() to reference any specific position in the ordered window.

➕ Combine First Value with Aggregates

Sometimes you want to combine window functions with aggregate functions — for example, to add row-level context (like a customer's first order date) before summarizing their full history.

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 pattern has two steps:

1. Stage 1: The CTE (Anchored) uses FIRST_VALUE() to add each customer’s starting date to every row.
2. Stage 2: The outer query aggregates the data by customer to get totals and date ranges.

⚠️ Important: You can’t mix window functions and aggregates directly in the same SELECT clause. For example, this version will fail:

SELECT CustomerID,
       FIRST_VALUE(OrderDate) OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS StartDate,
       MIN(OrderDate) AS FirstOrder
FROM Orders
GROUP BY CustomerID;

❌ Error: “column 'orders.orderdate' must appear in the GROUP BY clause or be used in an aggregate function.”

That’s why the CTE (or a subquery) is necessary — it separates the windowing step from the aggregation step, allowing SQL to first compute the per-row window function and then safely collapse rows using aggregates.

🧰 Debugging Tips for FIRST VALUE, LAST VALUE and NTH VALUE

• 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

See also: Frame Clauses, Aggregate Window Functions, LEAD / LAG