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

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 belong开发者_运维技巧s 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.

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

select a.OrderID, a.Status
from TblEvents a
where a.EventDate =
 (select max(b.EventDate)
  from TblEvents b
  where b.OrderId = a.OrderID)

Note this will return multiple rows if more than one record for an Order has the same lastest EventDate.

SELECT q.OrderID, q.Status
    FROM (SELECT e.OrderID, e.Status, 
                 ROW_NUMBER() OVER (PARTITION BY e.OrderID ORDER BY EventDate DESC) as RowNum)
              FROM tblEvents e) q
    WHERE q.RowNum = 1

The same query using a CTE:

;WITH cteRowNum AS (
    SELECT e.OrderID, e.Status, 
           ROW_NUMBER() OVER (PARTITION BY e.OrderID ORDER BY EventDate DESC) as RowNum
        FROM tblEvents e
)
SELECT q.OrderID, q.Status
    FROM cteRowNum q
    WHERE q.RowNum = 1

SELECT TblORDER.ORDERID, MAX(tblEvent.EventDate), tblEvent.Status FROM TblOrder INNER JOIN tblEvent ON tblEvent.OrderID = tbOrder.ORDERID GROUP BY TblORDER.ORDERID... something like that ?

Try This...

SELECT DISTINCT(OrderId) OrderId, Status 
FROM tblEvents   
ORDER BY EventDate

Select * from TblEvents Where EventID IN(
  Select MAX(EventID) from TblEvents Group by OrderID
)

• Now Join this with any table you want