How to select from table by comparing multiple rows with same key

Question

I have below table KEY_STATUS.

|| KEY || STATUS ||
| 0001 | FAILED |
| 0001 | COMPLETED |
| 0002 | FAILED |
| 0003 | COMPLETED |
| 0002 | FAILED |
| 0004 | STARTED |

I would like to select keys with only FAILED state. That means I will have to exclude 0001 as it has both FAILED and COMPLETED, also exclude 0003 as it has COMPLETED state. Output should be

|| KEY || STATUS ||
| 0002 | FAILED |

What approach should I take in Oracle to achieve above?

Answer 1

Assuming only two possible values for status , eliminate all keys that has more than one row and filter for failed status

SELECT *
FROM key_status
WHERE status='FAILED'
 AND key <>
 (SELECT key
 FROM key_status
 --WHERE status IN('COMPLETED','FAILED') 
 GROUP BY key
 HAVING COUNT(key) >1) 

• Sam: Thankyou Kumar for the reply. I forgot one additional scenario. There can be multiple rows with same state and if it is FAILED we will still need to retrieve them as single key. Edited above question to include another row for 0002 as FAILED. Do we need any change to your query to make it work with these kind of duplicate rows?
• Sam: I guess I got it. I can use below. Thankyou. none SELECT * FROM key_status WHERE status='FAILED' AND key <> (SELECT key FROM key_status WHERE status <> 'FAILED' GROUP BY key HAVING COUNT(key) >0)