Creating a query to retrieve certain rows in Mysql.

Question

I have an orders table with a schema like this.

CREATE TABLE orders (
    order_id        int NOT NULL AUTO_INCREMENT,
    customer_id     int unsigned NOT NULL DEFAULT 0,
    order_date      int NOT NULL
)

I'm trying to retrieve a list of each customers most recent order. The table can have multiple orders by a customer, and each order has a primary key of order_id.

Answer 1

As you use AUTO_INCREMENT for order_id the highest order_id per customer_id should be the latest:

Select customer_id, Max( order_id ) max_order_id
From orders
Group By customer_id

---

If you need all columns of that rows, you'd join a sub-select (edited to not depend on order_id):

Select o.customer_id, o.order_id, o.order_date
From orders o
Join (
    Select customer_id, Max( order_date ) max_order_date
    From orders
    Group By customer_id
) x On ( x.customer_id = c.customer_id AND x.max_order_date = o.order_date )

Answer 2

select o.*
from (
    select customer_id, max(order_date) as MaxOrderDate
    from orders
    group by customer_id
) om
inner join order o on om.customer_id = o.customer_id and om.MaxOrderDate = m.order_date

Answer 3

SELECT * FROM orders WHERE (customer_id, order_date) = (select customer_id, max(order_date) from orders group by customer_id)