Skip to content
Advertisement

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:

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:

You could also use window functions:

User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement