Skip to content
Advertisement

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:

The query selects the most recent date, but does not select the amount associated with the most recent order_date.

Table declarations:

Advertisement

Answer

I would recommend a correlated subquery in the where clause:

For performance, you want an index on orders(customer_id, order_date).

User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement