sqlsql-servermaxdate

How to group by max date with un-unique field

What would be the best SELECT statement to get max Event date to achieve the below mentioned Desired Table?

Scenario : An Order Can have multiple events in its lifecycle.. like Order Cancelled Event, order placed on hold event.. etc.. and each event records a note in the notes table.

Below are the Event & Notes Table.. and I only want the last recorded event and it's note.

Table 1 : EVENT Table

enter image description here

Table 2 : NOTES Table

enter image description here

Desired Table

enter image description here

UPDATE : In the desire table I only want to get the max event date, and the note for that max event for each order ID where Event Reason is 'On Hold'. So the OrderID will be unique identifier in the desired table

Solution 
  • ;WITH cte As (
Select Notes.OrderId, Tmp.MaxDate, Notes.Note, ROW_NUMBER() OVER(PARTITION BY Notes.OrderId Order By Notes.Note ) As rowNum
From
(Select EventId ,MAX(EventDate) As MaxDate 
From EventTable
Where EventReason = 'OnHold' 
Group By EventId ) Tmp
Inner Join Notes On Tmp.EventId = Notes.EventId 
)

Select OrderId, MaxDate, Note
From cte
Where RowNum = 1