How to write an SQL statement that gets a row if the rest of the rows with the same userID are not a certain condition?

Given the following table:

userId |    State     |  Number
--------------------------------
 123   |   waiting    |   3
 123   |  processing  |   2
 123   |  completed   |   1

How can I get the row for a single user with State = waiting state given a Number (let’s say 3 in this case) only if there is no row for the same user with a lower Number and is in the processing state.

In this example, the query would not return a row. If row labeled Number = 2 was State = completed (instead of State = processing), then the query would return the whole row labeled Number = 3.

Answer

If I understand correctly, you can use not exists:

select t.*
from t
where t.state = 'waiting' and
      not exists (select 1
                  from t t2
                  where t2.userId = t.userId and
                        t2.state = 'processing' and
                        t2.number < 3
                 );

You could also use window functions:

select t.*
from (select t.*,
             min(number) filter (where state = 'processing') as min_processing
      from t
     ) t
where min_processing < 3 and state = 'waiting';