Orders Placed on the Most Recent Date per Customer

Return the latest order (id, customer_id, order_date) of every customer using a correlated subquery. If a customer has two orders on their latest date, return both. Order by id.

Table schema

Orders(id INT PRIMARY KEY, customer_id INT, order_date DATE)

Sample data

id | customer_id | order_date
1 | 1 | 2026-01-01
2 | 1 | 2026-02-01
3 | 2 | 2026-01-15
4 | 2 | 2026-01-15