🧠 FIRST_VALUE(): Get the First Row in a Group

Xorthax can now look backward and forward between rows. But sometimes he wants something simpler:

What is the first value in this group?

That's what FIRST_VALUE() does.

🚀 FIRST_VALUE() + OVER()

FIRST_VALUE() returns the first value in a sequence of rows.

FIRST_VALUE(column) OVER (
  PARTITION BY column
  ORDER BY column
)

Here’s why:

• PARTITION BY defines the group
• ORDER BY defines which row is first

📊 Example: First Order Amount per Customer

🧾 Sample Data

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

Result:

Each row now shows the first 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.

FIRST_VALUE() looks at the first row in that ordered sequence.

That value is then repeated for every row in the group.

If you change the order, you change what counts as first. And you must have an ORDER BY so SQL knows what is first!

🧪 Practice What You Just Learned

SELECT orderid, orderdate, totalamount, FIRST_VALUE(totalamount) OVER (ORDER BY orderdate) AS first_amount FROM orders ORDER BY orderdate;

➡️ Next Step

FIRST_VALUE() gives you the beginning of a sequence.

Next, you'll learn how to get the value at the end.

Want to go deeper?