How to look for missing rows within a group?

Consider the data below. I am trying to find situations where in a specific RequestID, there is an Attempt: 0, but Attempt: 2 is missing.

I’ve tried looking for Attempt: 0 with a WHERE predicate and doing NOT EXISTS ... Attempt: 2 in a subquery, but it doesn’t return the right data.

How do I find the RequestIDs with missing Attempt: 2?

ID      Message       RequestID
635828  Attempt: 0    1
635968  Attempt: 1    1
641085  Attempt: 2    1
641230  Attempt: 3    1
643859  Attempt: 0    2
645991  Attempt: 1    2
650255  Attempt: 3    2
652388  Attempt: 0    3
654520  Attempt: 1    3
658785  Attempt: 3    3

​x
 
ID      Message       RequestID635828  Attempt: 0    1635968  Attempt: 1    1641085  Attempt: 2    1641230  Attempt: 3    1643859  Attempt: 0    2645991  Attempt: 1    2650255  Attempt: 3    2652388  Attempt: 0    3654520  Attempt: 1    3658785  Attempt: 3    3​

Answer

You can use not exists like this:

select t.*
from t
where t.message = 'Attempt: 0' and
      not exists (select 1
                  from t t2
                  where t2.requestid = t.requestid and
                        t2.message = 'Attempt: 2'
                 );

 
select t.*from twhere t.message = 'Attempt: 0' and      not exists (select 1                  from t t2                  where t2.requestid = t.requestid and                        t2.message = 'Attempt: 2'                 );​

Another possibility is aggregation:

select requestid
from t
where message in ('Attempt: 0', 'Attempt: 2')
group by requestid
having sum(case when message = 'Attempt: 2' then 1 else 0 end) = 0;

 
select requestidfrom twhere message in ('Attempt: 0', 'Attempt: 2')group by requestidhaving sum(case when message = 'Attempt: 2' then 1 else 0 end) = 0;​

Advertisement

Answer