Beginner

675. JOIN with FIRST_VALUE

Use FIRST_VALUE with JOINed records to retrieve the first value within a group.

📘 Lesson 675 of 728 ⏱ 14 min 🎯 Advanced

FIRST_VALUE returns the value from the first row of a specified window according to the defined ordering.

It can provide the first transaction, first status, or first recorded value alongside every detail row.

Show every customer order together with the customer's first order date.

SQL 
SELECT
    c.CustomerName,
    o.OrderDate,
    FIRST_VALUE(o.OrderDate) OVER (
        PARTITION BY o.CustomerID
        ORDER BY o.OrderDate, o.OrderID
    ) AS FirstOrderDate
FROM Customers c
INNER JOIN Orders o
    ON c.CustomerID = o.CustomerID;

Used in customer lifecycle analysis, cohort analysis, transaction history, and reporting.

  • FIRST_VALUE returns the first value in the window.
  • ORDER BY determines what 'first' means.
  • PARTITION BY separates groups.
  • Detail rows remain visible.
  • Useful for lifecycle and historical analysis.
What does FIRST_VALUE return?

It returns the value from the first row according to the window ordering.

How do you define the first record?

Use ORDER BY inside the window to specify the required sequence.

Have you finished this lesson?

Introduction to SQL – All Lessons