Select Query with MAX
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.
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
)
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.
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)
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...
精彩评论