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.

D'Arcy Rittich · Accepted Answer · 2010-03-09 19:23:28Z

1

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

answered Mar 9, 2010 at 19:23

D'Arcy Rittich

172k41 gold badges298 silver badges287 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Peter Lang · Accepted Answer · 2010-03-09 19:33:11Z

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 )

edited Mar 9, 2010 at 19:33

answered Mar 9, 2010 at 19:21

Peter Lang

55.7k29 gold badges153 silver badges165 bronze badges

5 Comments

Vidar Vestnes Over a year ago

+1 for a simple and clean query without subqueries. this query should work for most recent orders as well by changing order_id to order_date

Eric Petroelje Over a year ago

@Peter - because you are introducing two hidden dependencies - the first is that order_id is always increasing (what if it rolls over?) and the second is that the order_date cannot be changed (if it did, it would have no effect on the query). On the plus side, both of those things are pretty unlikely, and the performance of that query would likely be better than any alternative. Still scares me a bit though.

Peter Lang Over a year ago

@PHP_Jedi: Thanks. I assumed that orders are inserted in the order of order_date, if this is not the case, then your suggestion is right.

Aglystas Over a year ago

Thanks, I didn't realize you can join on sub-selects like that.

Peter Lang Over a year ago

@Eric Petroelje: Agreed, I edited the second part of my answer.

Lloyd · Accepted Answer · 2010-03-09 19:25:03Z

0

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

answered Mar 9, 2010 at 19:25

Lloyd

1,4036 gold badges22 silver badges40 bronze badges

1 Comment

Vidar Vestnes Over a year ago

This query uses a subquery... subqueries may be slow if the subqeury result is large.

Collectives™ on Stack Overflow

Creating a query to retrieve certain rows in Mysql

3 Answers 3

Comments

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related