0

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?

0

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) 
2
  • 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
    Apr 30 '20 at 3:46
  • 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)
    – Sam
    Apr 30 '20 at 3:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.