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
Table 2 : NOTES Table
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
;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