Enquiry select sql "advanced level"-CodePudding

I have the table below and I want to write a query so that it shows me the tickets that have the description of the event "resolve" and also shows the last description of the event that ticket got it and if it does not have a description of the event that is "resolve" it only shows me the last description of the event that the ticket got

Ticket Id   EventId     EventDate    EventDescription
1             1        2021-01-06        create
1             2        2021-01-07        resolve
2             3        2021-01-06        create
3             4        2021-01-15        create
1             5        2021-01-09        close
1             6        2021-01-12        Re-open
2             7        2021-01-10        Assign
2             8        2021-01-22        resolve

The table below will help you find the result I want

Ticket Id   EventId     EventDate    EventDescription
1             2        2021-01-07        resolve
3             4        2021-01-15        create
1             6        2021-01-12        Re-open
2             8        2021-01-22        resolve

I apologize if my question is not clear because my English is poor

CodePudding user response：

You can use row_number to identify the rows for each TicketId

with r as (
    select * , Row_Number() over(partition by ticketid order by eventid desc) rn
    from t
)
select TicketId, EventId, EventDescription
from r
where rn=1 or EventDescription='resolve' 
order by eventid