🧠 LAST_VALUE(): Get the Last Row in a Group

Xorthax now knows how to get the first value in a group. But what if he wants the value at the end?

That's what LAST_VALUE() does - but it requires one important detail.

🚀 LAST_VALUE() + OVER()

LAST_VALUE() returns the last value in a sequence of rows.

LAST_VALUE(column) OVER (
  PARTITION BY column
  ORDER BY column
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)

This works because:

• PARTITION BY defines the group
• ORDER BY defines the sequence
• The frame includes all rows in the group

📊 Example: Last Order Amount per Customer

🧾 Sample Data

SELECT CustomerID, OrderDate, Amount,
       LAST_VALUE(Amount) OVER (
         PARTITION BY CustomerID
         ORDER BY OrderDate
         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS LastAmount
FROM Orders;

Result:

Each row now shows the last value within its customer group.

What's Happening Here?

PARTITION BY CustomerID creates a group for each customer.

ORDER BY OrderDate defines the sequence within each group.

The window frame includes all rows in the group, so LAST_VALUE() can see the true last row.

Important: If you leave out the window frame, SQL only looks at rows up to the current row.

That means LAST_VALUE() will return the current row's value—not the actual last value in the group.

🧪 Practice What You Just Learned

SELECT orderid, orderdate, totalamount, LAST_VALUE(totalamount) OVER (ORDER BY orderdate ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_amount FROM orders ORDER BY orderdate;

➡️ Next Step

FIRST_VALUE() and LAST_VALUE() give you the edges of a sequence.

Next, you'll learn how to get a value from any position.

Want to go deeper?