How to group by max date with un-unique field

Question

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

Desired 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

I want to see all the orderID where Event reason is on hold and the latest event only. — Lokapedia, Sep 11 '16 at 06:16
This information should be a part of your question, not a side note. — Tim Biegeleisen, Sep 11 '16 at 06:22
based on your comment and your updated comment, the desired table has wrong data, either in the date values or in the note values, how come the note _Order Cancelled due to no payment_ should appears and you only take the Event Reason = 'OnHold' while it belongs to 'Cancelled' event reason — Monah, Sep 11 '16 at 07:26

Mudassir Hasan · Accepted Answer · 2016-09-11T06:44:55.483

1

;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

edited Sep 11 '16 at 06:44

answered Sep 11 '16 at 06:32

Mudassir Hasan

28,083
20
99
133

Thanks mhasan, but still getting repating orderID. – Lokapedia Sep 11 '16 at 06:39
Check now .. i have used cte to generate rownumbers for each order id and select one row – Mudassir Hasan Sep 11 '16 at 06:45

Sandip - Frontend Developer · Answer 2 · 2016-09-11T08:24:03.693

0

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

edited Sep 11 '16 at 08:24

answered Sep 11 '16 at 06:01

Sandip - Frontend Developer

14,397
4
35
61

Thanks Sandip, I forgot to mentioned I only want those orders where Event reason is On Hold – Lokapedia Sep 11 '16 at 06:14
I tried your query but it still the orderID is repeating. – Lokapedia Sep 11 '16 at 06:15
it should not repeat as i have partition on it, you can write any where condition as per your requirement but you have to use partition to resolve your query – Sandip - Frontend Developer Sep 11 '16 at 06:34
Check now and try, actually you have wrong info in requirement – Sandip - Frontend Developer Sep 11 '16 at 08:24

score 0 · Answer 3 · edited Oct 27 '21 at 22:00

0

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;

edited Oct 27 '21 at 22:00

halfer

19,824
17
99
186

answered Sep 11 '16 at 08:29

Jim Macaulay

4,709
4
28
53

How to group by max date with un-unique field

3 Answers3