title: Select Query with MAX
body: <p>i've got a big Problem and i was trying the whole day and did not find any Solution. Hope you can help me?</p>
  
  <p>I have two tables:</p>
  
  <p>The first one named "orders":</p>
  
  <pre><code>orders_id | orders_date | .....
  1           xxxxxxxxxx
  2           xxxxxxxxxx
  3           xxxxxxxxxx
  </code></pre>
  
  <p>The second is "orders_history":</p>
  
  <pre><code>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
  </code></pre>
  
  <p>So now i want to have all orders where order_status_id = '1'</p>
  
  <p>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:</p>
  
  <pre><code>SELECT orders.*, orders_history.* FROM orders, orders_history WHERE orders_history.order_status_id &lt;= '1'
  </code></pre>
  
  <p>But then i also get Order with order_id 1</p>
  
  <p>Hope you can help. Thank you!</p>
  
  <p>Sascha</p>
  
  <p>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 <i>currently</i> have an order status of 1. Order ID# 1 currently has a status of 2, so it should not be included in the results.</p>
  
  <p>Assumably, order status goes up over time and never goes down, so that the order status and date_added will constantly increase.</p>