title: SQL Select MIN in Subquery returns multiple records
body: <p>I have a Table with Orders</p>
  
  <pre><code>+----+-------------+--------+
  | ID | OrderNumber | CartId |
  +----+-------------+--------+
  |1   | ABDE45677   | 1      |
  |2   | ABFRTG456   | 2      |
  +----+-------------+--------+
  </code></pre>
  
  <p>One with cart items for each cart (which belongs to an order)</p>
  
  <pre><code>+----+--------+-----------+
  | ID | CartId | ProductId |
  +----+--------+-----------+
  |1   | 1      | 34577     |
  |2   | 1      | 26846     |
  |3   | 2      | 59055     |
  |4   | 3      | 43567     |
  +----+--------+-----------+
  </code></pre>
  
  <p>And one with cart item events (such as 'sent', 'shipped', 'returned', etc)</p>
  
  <pre><code>+----+---------------------+------------+------+
  | ID | EventDate           | CartItemId | Type |
  +----+---------------------+------------+------+
  |1   | 2016-07-12 11:54:12 | 1          |1     |
  |2   | 2016-07-12 12:01:12 | 1          |3     |
  |3   | 2016-07-12 10:10:00 | 2          |1     |
  |4   | 2016-07-12 11:00:00 | 2          |2     |
  |5   | 2016-07-13 13:00:00 | 2          |4     |
  |6   | 2016-07-12 12:00:00 | 3          |1     |
  |7   | 2016-07-14 12:30:12 | 3          |2     |
  +----+---------------------+------------+------|
  </code></pre>
  
  <p>The relationships are</p>
  
  <pre><code>Orders &gt;hasmany&gt; Carts &gt;hasmany&gt; CartItems &gt;hasmany&gt; CartItemEvents
  </code></pre>
  
  <p>I now want to get all Orders with a 'status' column at the end. This column should display the lowest 'type' value of all cart item events belonging to the order. I only want to get the latest event for each order Item.</p>
  
  <pre><code>SELECT o.ID, 
         o.OrderNumber,
         (SELECT MIN(type) FROM CartItemEvents cie WHERE cie.CartItemId=ci.Id 
                                          AND   cie.EventDate=
                                          (SELECT MAX(EventDate) FROM CartItemEvents WHERE ci.id=CartItemId)) 
         AS 'status'
  FROM Orders o
  LEFT JOIN CartItems ci on o.CartId=ci.CartId
  </code></pre>
  
  <p>Unfortunately, I get multiple types for each order (one type for each item)</p>
  
  <pre><code>+----+-------------+--------+
  | ID | OrderNumber | Status |
  +----+-------------+--------+
  |1   | ABDE45677   | 3      |
  |1   | ABDE45677   | 4      |
  |2   | ABFRTG456   | 2      |
  +----+-------------+--------+
  </code></pre>
  
  <p>It seems like the function</p>
  
  <pre><code>SELECT MIN(type)
  </code></pre>
  
  <p>has no effect here, since removing MIN() brings the same result.</p>
  
  <p>How can I get only the minimum type for each order?
  Maybe I have too many nested subqueries here?</p>