How to check rows for continuity?

I have an operation that if it fails - it retries 5 times and then gives up. Resulting in following log table:

LogId   OpId  Message
1       4     Retry 1...Failed
2       4     Retry 2...Failed
3       4     Retry 3...Failed
4       4     Retry 4...Failed
5       4     Retry 5...Failed
6       4     Max Retries exceeded - giving up

Sometimes, it will succeed after retry, which means that I'll never see the Max Retries exceeded - giving up entry within that OpId.

And that I what I am trying to identify. Operations that were forced to go into retries (e.g there is a Retry X... entry), but there isn't a Max Retries exceeded - giving up entry because the retry succeeded at some point.

I tried using Window functions, and I think that might the way to go but I am not sure how to actually identify what I want.

P.S. Added auto-incrementing field per @GMB

Solution

For this dataset, you might be able to just use aggregation:

select opId
from mytable
group by opId
having 
    max(case when message like 'Retry%' then 1 end) = 1
    and max(case when message = 'Max Retries exceeded - giving up' then 1 end) is null

This gives you the list of opId for which at least one message starts with 'Retry' and that have no message equal to 'Max Retries exceeded - giving up'.