Beginner

618. First Record Using JOIN

Learn how to retrieve the first related record for each entity.

📘 Lesson 618 of 728 ⏱ 14 min 🎯 Advanced

The first-record pattern identifies the earliest related row for each entity using ordering and ranking techniques.

Finding the first transaction, first login, first purchase, or first status is common in business analytics.

Find each customer's first order.

SQL 
WITH RankedOrders AS (
    SELECT
        o.*,
        ROW_NUMBER() OVER (
            PARTITION BY o.CustomerID
            ORDER BY o.OrderDate ASC, o.OrderID ASC
        ) AS rn
    FROM Orders o
)
SELECT
    c.CustomerName,
    r.OrderID,
    r.OrderDate
FROM Customers c
INNER JOIN RankedOrders r
    ON c.CustomerID = r.CustomerID
WHERE r.rn = 1;

Used in customer acquisition analysis, onboarding reports, cohort analysis, and lifecycle analytics.

  • Earliest records can be found with ascending order.
  • ROW_NUMBER provides one record per entity.
  • A tie-breaker improves consistency.
  • The pattern works with many types of history.
  • First-record logic is useful in cohort analysis.
How do you find the first order for each customer?

Use ROW_NUMBER partitioned by customer and order by order date ascending, then select row number 1.

Where is first-record analysis useful?

It is useful for customer acquisition, first transactions, onboarding, and lifecycle analysis.

Have you finished this lesson?

Introduction to SQL – All Lessons