Given the following table:
x
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
.
Advertisement
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';