CODEWITHSUNDEEP
mysqlsql
Ninius86
Ninius86

Reputation: 295

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

In the table REPORT there are following 3 columns:

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

Upvotes: 0

Views: 46

Answers (3)

forpas
forpas

Reputation: 164139

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   |

Upvotes: 2

Sleeper
Sleeper

Reputation: 81

Out of my head, probably with few typos, and probably not the best, but should be easy to understand...

SELECT n.RID FROM (
SELECT RID, TYPE, MIN(DATETIME) AS FirstAdd
FROM REPORT
WHERE TYPE = 'New'
GROUP BY RID, TYPE) n INNER JOIN (
SELECT RID, TYPE, MAX(DATETIME) AS LastCancel
FROM REPORT
WHERE TYPE = 'Cancel'
GROUP BY RID, TYPE) c ON n.RID = c.RID 
AND n.FirstAdd < c.LastCancel

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270191

I would get the latest type using a correlated subquery. Then check for "cancel":

select t.*
from t
where t.timestamp = (select max(t2.timestamp)
                     from t t2
                     where t2.rid = t.rid
                    ) and
      t.type = 'Cancel';

If you just want the rid and date, then another fun way uses aggregation:

select rid, max(timestamp)
from t
group by rid
having max(timestamp) = max(case when type = 'Cancel' then timestamp end);

The logic here is to get timestamps where the maximum date is also the maximum cancel date.

Upvotes: 1

Related Questions