CODEWITHSUNDEEP
sqlsql-servermaxdate
Lokapedia
Lokapedia

Reputation: 105

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

Upvotes: 0

Views: 78

Answers (3)

Jim Macaulay
Jim Macaulay

Reputation: 5165

You can use below query:

SELECT NT.ODER_ID, MAX(EV.EVENT_DATE), NT.NOTE
      FROM EVENT EV, NOTES NT WHERE
      EV.EVENT_ID = NT.EVENT_ID
 GROUP BY NT.ODER_ID, NT.NOTE 
 ORDER BY NT.ODER_ID;

Upvotes: 0

Sandip - Frontend Developer
Sandip - Frontend Developer

Reputation: 14699

Use Partition By:

SELECT
    X.OrderId,
    X.EventDate,
    X.Note,
FROM
(
    SELECT 
        N.OrderId,
        E.EventDate,
        N.Note,
        ROW_NUMBER() OVER(Partition By N.OrderId Order By N.OrderId,N.NoteDate DESC) AS PartNo
    FROM NOTES N
    LEFT JOIN dbo.[EVENT] E ON E.EventId=N.EventId
    WHERE N.EventId IS NOT NULL 
    AND N.Note like '%on hold%'
)X
WHERE X.PartNo=1

Upvotes: 0

Mudassir Hasan
Mudassir Hasan

Reputation: 28781

;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

Upvotes: 1

Related Questions