Select data from specific row of grouped and joined table in MySQL

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:

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)
);

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).

Advertisement

Answer