I have two tables, customers and orders which are inner joined. A customer can have several orders associated with them. In my selection, I then group by customers.id. I need to select the most recent order of each customer, but also the amount of money spent in that order. Currently, I can select the most recent order_date but do not know how to select the amount in the same row as the order_date.
This is my current query:
x
SELECT
first_name,
last_name,
email,
MAX(order_date) AS recent_order,
amount -- this needs to select amount associated with recent_order
FROM customers
JOIN orders
ON customers.id = orders.customer_id
GROUP BY customers.id;
The query selects the most recent date, but does not select the amount associated with the most recent order_date.
Table declarations:
CREATE TABLE customers (
id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(100),
last_name VARCHAR(100),
email VARCHAR(100)
);
CREATE TABLE orders (
id INT AUTO_INCREMENT PRIMARY KEY,
order_date DATE,
amount DECIMAL(8,2),
customer_id INT,
FOREIGN KEY(customer_id) REFERENCES customers(id)
);
Advertisement
Answer
I would recommend a correlated subquery in the where
clause:
SELECT c.*, o.* -- or whatever columns you want
FROM customers c JOIN
orders o
ON c.id = o.customer_id
WHERE o.order_date = (SELECT max(o2.order_date)
FROM orders o2
WHERE o2.customer_id = o.customer_id
);
For performance, you want an index on orders(customer_id, order_date)
.