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