SQL query to find status of the last event of an order

Question

I have two tables (in a SQL Server database) as following:

TblOrders
OrderID (PK)
(some more fields)

TblEvents
EventID (PK)
OrderID (FK) (linked to OrderID of TblOrders)
EventDate
Status

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).

I need a SQL query that finds for each OrderID in TblOrders the status of the latest event among all its events. For Example:

Input tables:

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

Result of the query:

OrderID Status
      1      2
      2      2
      3      3

(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.)

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.

Answer 1

select a.OrderID, e.Status
from (
   select o.OrderID, max(e.EventDate) latestDate
   from TblOrders o
   inner join TblEvents e on o.OrderID = e.OrderID
   group by o.OrderID
   ) a
inner join TblEvents e on e.OrderID = a.OrderID
where e.EventDate = a.latestDate