Select Query with MAX

Question

Hey Guys,

i've got a big Problem and i was trying the whole day and did not find any Solution. Hope you can help me?

I have two tables:

The first one named "orders":

orders_id | orders_date | .....
1           xxxxxxxxxx
2           xxxxxxxxxx
3           xxxxxxxxxx

The second is "orders_history":

orders_id | order_status_id | date_added
1           1                 2009-10-01
1           2                 2010-01-01
2           1                 2010-02-01
3           1                 2010-02-01

So now i want to have all orders where order_status_id = '1'

I have tried with MAX, HAVING, GROUP BY, ... Subselects also, but i haven't found any solution. I know it's not very hard, but i'm finished... Is it something like:

SELECT orders.*, orders_history.* FROM orders, orders_history WHERE orders_history.order_status_id <= '1'

But then i also get Order with order_id 1

Hope you can help. Thank you!

Sascha

To further clarify, the poster's 'orders_history' table keeps track of the state of all orders over time. The goal is a query that will find all orders that currently have an order status of 1. Order ID# 1 currently has a status of 2, so it should not be included in the results.

Assumably, order status goes up over time and never goes down, so that the order status and date_added will constantly increase.

Answer 1

Edited after discussion in comments (changed the where clause):

SELECT orders.*, orders_history.* 
FROM orders INNER JOIN orders_history
 ON orders.orders_id = orders_history.orders_id 
WHERE orders.orders_id IN 
   (SELECT orders_id FROM orders_history 
   GROUP BY orders_id 
   HAVING MAX(order_status_id) = 1)

Answer 2

select o.*, oh.* 
from orders o
inner join orders_history oh on oh.orders_id = o.orders_id
where oh_orders_status = 1

should do the trick. It's a while since I touched mysql though, so I don't know if your orders_status should be in quotes - I'd guess not if it is an int...

Answer 3

I'm not surprised you had trouble getting this to work - it's a very tricky type of query where you must 'GROUP BY' and find the MAX and also all the other corresponding values in the same row. This is a common request, and it often surprises people that it's actually quite difficult to express this in SQL. Here's one way to do it in MySQL:

SELECT T2.orders_id FROM (
    SELECT orders_id, MAX(date_added) AS date_added
    FROM orders_history
    GROUP BY orders_id
) AS T1
JOIN orders_history T2
ON T1.orders_id = T2.orders_id AND T1.date_added = T2.date_added
GROUP BY T2.orders_id, T2.date_added
HAVING MAX(order_status_id) = 1

Here I am assuming that:

• orders_id, date_added is not unique.
• orders_id, date_added, order_status_id is unique.

If not the second assumption is not true, add DISTINCT after the first SELECT.

Here are the results I get for your test data:

2
3

You can join this to your orders table if you want to fetch extra information about each order.

Answer 4

This should do it for you:

SELECT * 
FROM orders
   , orders_history
WHERE orders.orders_id = orders_history.orders_id
AND orders.orders_id IN (
  SELECT orders_id 
    FROM orders_history
   GROUP BY orders_id
   HAVING MAX(order_status_id) = 1
)