Schema and sample data
| order_id | customer_id | order_date | amount |
|---|---|---|---|
| 1 | 101 | 2024-01-05 | 250.00 |
| 2 | 102 | 2024-01-08 | 120.00 |
| 3 | 101 | 2024-02-10 | 90.00 |
| 4 | 103 | 2024-01-15 | 500.00 |
| 5 | 102 | 2024-03-01 | 300.00 |
| 6 | 103 | 2023-12-20 | 75.00 |
Show setup SQL (copy to run)
CREATE TABLE Orders (
order_id INT PRIMARY KEY,
customer_id INT NOT NULL,
order_date DATE NOT NULL,
amount DECIMAL(10,2) NOT NULL
);
INSERT INTO Orders (order_id, customer_id, order_date, amount) VALUES
(1, 101, '2024-01-05', 250.00),
(2, 102, '2024-01-08', 120.00),
(3, 101, '2024-02-10', 90.00),
(4, 103, '2024-01-15', 500.00),
(5, 102, '2024-03-01', 300.00),
(6, 103, '2023-12-20', 75.00);From the Orders table, return the first order (the one with the earliest order_date) placed by each customer. Return the columns customer_id, order_id, order_date and amount, one row per customer. Sort the result by customer_id ascending. You can assume each customer has a single earliest order date.
Expected result
| customer_id | order_id | order_date | amount |
|---|---|---|---|
| 101 | 1 | 2024-01-05 | 250.00 |
| 102 | 2 | 2024-01-08 | 120.00 |
| 103 | 6 | 2023-12-20 | 75.00 |
Show hint
Number the orders inside each customer so that the earliest date gets number 1. A window function like ROW_NUMBER() with PARTITION BY customer_id ORDER BY order_date does exactly this. You cannot filter on a window function in the same WHERE, so compute it in a CTE (or subquery) first, then keep the rows where the number is 1.
Try to write the query yourself before you open this.
Show solution and explanation
WITH ranked AS (
SELECT
customer_id,
order_id,
order_date,
amount,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY order_date
) AS rn
FROM Orders
)
SELECT customer_id, order_id, order_date, amount
FROM ranked
WHERE rn = 1
ORDER BY customer_id;The window function ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) restarts its count for every customer and hands out 1, 2, 3, ... in date order. So the earliest order for each customer always receives rn = 1. See ROW_NUMBER() for the full syntax and how the partition and ordering clauses work.
You cannot reference the alias rn in a WHERE clause on the same level, because window functions are evaluated after WHERE in the logical query processing order. Wrapping the calculation in a CTE named ranked lets the outer query treat rn as an ordinary column and filter WHERE rn = 1. The final ORDER BY customer_id just makes the output deterministic.
A common alternative is a correlated subquery: WHERE order_date = (SELECT MIN(order_date) FROM Orders o2 WHERE o2.customer_id = o.customer_id). This also works, but if a customer had two orders on the same earliest date it would return both rows, whereas ROW_NUMBER() guarantees exactly one row per customer. Use RANK() instead of ROW_NUMBER() if you actually want all ties.