How to get entries which are grouped and satisfy restriction within the group?

In the table REPORT there are following 3 columns:

• RID – report id, which is not unique
• TYPE – can be either new or cancel, report is identified by RID, so one report can have multiple cancellations and multiple new entries
• TIMESTAMP – is just a timestamp of when entry arrived

Example below:

 RID |  Type   |    TIMESTAMP      |
-----+---------+-------------------+
4    |  New    | 2019-10-27 10:35  |
4    |  Cancel | 2019-10-27 09:35  |
3    |  Cancel | 2019-10-27 07:35  |
2    |  New    | 2019-10-27 07:35  |
1    |  Cancel | 2019-10-27 09:35  |
1    |  Cancel | 2019-10-27 08:35  |
1    |  New    | 2019-10-27 07:35  |

I’d like to get all reports which at some point were created and then canceled, so that the latest state is canceled. It is possible to have cancellations of non-existed reports, or entries with first cancellations and then new entries, all of those should be excluded.

My attempt so far was to use nested query to get all cancellations, which have corresponding new entry, but do not know how to consider their timestamps into consideration, to exclude entries which have sequence cancel->new

SELECT
    RID
FROM
    REPORT
WHERE
    TYPE = 'Cancel'
    AND RID IN (
        SELECT
            RID
        FROM
            REPORT
        WHERE
            TYPE = 'New'
    );

My expectation from the example is to get RID 1, I’m interested in only RIDs.

Using: MySQL 5.7.27 InnoDB

Answer

With EXISTS:

select distinct r.rid
from report r
where r.type = 'Cancel' 
and exists (
  select 1 from report  
  where rid = r.rid and type='New' and timestamp < r.timestamp
) 

See the demo.
Or:

select rid
from report
group by rid
having 
  min(case when type = 'New' then timestamp end) <
  min(case when type = 'Cancel' then timestamp end)

See the demo.
Results:

| rid |
| --- |
| 1   |