SQL: When doing a join, how do you join on only the first row?

 To join on only the first row when performing a SQL join, you typically need to use a subquery or a window function to identify the "first row" for each group in the table you're joining with. Here's how you can do it:

Using ROW_NUMBER()

The ROW_NUMBER() window function assigns a unique number to each row within a partition, allowing you to isolate the first row:

WITH RankedRows AS (
    SELECT 
        t1.*, 
        ROW_NUMBER() OVER (PARTITION BY group_column ORDER BY sort_column ASC) AS row_num
    FROM table1 t1
)
SELECT 
    a.*, 
    b.*
FROM table2 a
JOIN RankedRows b
    ON a.join_column = b.join_column
   AND b.row_num = 1;

Explanation:

1. ROW_NUMBER(): This assigns a unique row number (row_num) to each row within a partition (PARTITION BY group_column) based on a specified order (ORDER BY sort_column ASC).
2. Filter to the First Row: By including AND b.row_num = 1 in the JOIN condition, only the first row of each partition is joined.

Example:

Tables:

Orders

order_id	customer_id	order_date
1	101	2025-01-15
2	101	2025-01-16
3	102	2025-01-14

Customers

customer_id	customer_name
101	Alice
102	Bob

Query:

WITH FirstOrder AS (
    SELECT 
        order_id, 
        customer_id, 
        order_date, 
        ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date ASC) AS row_num
    FROM Orders
)
SELECT 
    c.customer_name, 
    f.order_date
FROM Customers c
JOIN FirstOrder f
    ON c.customer_id = f.customer_id
   AND f.row_num = 1;

Result:

customer_name	order_date
Alice	2025-01-15
Bob	2025-01-14

This ensures you join only the first row of Orders for each customer_id. Adjust the ORDER BY clause to define what "first" means in your context.