title: SQL query to find status of the last event of an order
body: <p>I have two tables (in a SQL Server database) as following:</p>
  
  <blockquote>
    <p><strong>TblOrders</strong><br>
    OrderID (PK)<br>
    (some more fields)</p>
    
    <p><strong>TblEvents</strong><br>
    EventID (PK)<br>
    OrderID (FK) (linked to OrderID of TblOrders)<br>
    EventDate<br>
    Status</p>
  </blockquote>
  
  <p>Each event in TblEvents belongs to an order in TblOrders, and each event has a date and a 'status' (a numeric code). An order may have several events (at least one).</p>
  
  <p>I need a SQL query that finds for each OrderID in TblOrders the status of the <em>latest</em> event among all its events. For Example:</p>
  
  <p>Input tables:</p>
  
  <pre>
  TblOrders  
  =========
   OrderID
         1
         2
         3
  
  TblEvents  
  =========
   EventID OrderID EventDate  Status  
         1       1 01/02/2011      4
         2       1 02/02/2011      2
         3       2 03/02/2011      2
         4       3 03/02/2011      3
         5       2 01/02/2011      1
  </pre>
  
  <p>Result of the query:</p>
  
  <pre>
  OrderID Status
        1      2
        2      2
        3      3
  </pre>
  
  <p>(OrderID 2 has Status 2 because it has two events, 3 and 5, and the latest of them is Event 3 which has Status 2.)</p>
  
  <p>I hope I've explained myself clearly. I've tried to write the query for long time, but couldn't find the solution, so any help or hint will be welcomed.</p>