CODEWITHSUNDEEP
sqloracle-database
Abdul Kader
Abdul Kader

Reputation: 5842

Fetch records from table with value but not b

Below is my table structure , I am trying to get the UUID with status only as 'error' but no 'ok'.

I am doing something like this

select UUID from table where status='error'
MINUS
select UUID from table where status ='ok'

Is there a better way to doing this.

UUID STATUS
3aa0a4ed-fa9f-472d-a208-8173288880ee error
3aa0a4ed-fa9f-472d-a208-8173288880ee ok
d7d76644-d8ce-4c5a-af42-6bc021fd264a error
d7d76644-d8ce-4c5a-af42-6bc021fd264a ok
082324d0-24a6-47ca-9b55-94fad628903e error
082324d0-24a6-47ca-9b55-94fad628903e error
3aa0a4ed-fa9f-472d-a208-8173288880ee error
3aa0a4ed-fa9f-472d-a208-8173288880ee error

Upvotes: 1

Views: 43

Answers (3)

Shinto Joy
Shinto Joy

Reputation: 31

Best approach (in terms of performance) would be to use analytical function.

SELECT UUID FROM (
  SELECT t.UUID UUID,
      t.status status,
      ROW_NUMBER () OVER (PARTITION BY UUID ORDER BY status DESC) status_rnk
  FROM  table t)
WHERE status = 'error' AND status_rnk = 1;

Upvotes: 1

astentx
astentx

Reputation: 6751

If you have only two statuses, you may use aggregation with one table scan:

select uuid
from a
group by uuid
having min(status) = max(status)
  and min(status) = 'error'

| UUID                                 |
| :----------------------------------- |
| 082324d0-24a6-47ca-9b55-94fad628903e |

Or with conditional aggregation that may be extended to more statuses:

select uuid
from a
group by uuid
having count(decode(status, 'error', 1)) > 0 /*Has 'error' status*/
  and count(nullif(status, 'error')) = 0 /*And has no other statuses*/

| UUID                                 |
| :----------------------------------- |
| 082324d0-24a6-47ca-9b55-94fad628903e |

db<>fiddle here

Upvotes: 1

The Impaler
The Impaler

Reputation: 48780

You can use NOT EXISTS (<subquery>). For example:

select uuid
from my_table a
where status = 'error'
  and not exists (
    select 1 from my_table b where b.uuid = a.uuid and b.status = 'ok'
  )

Or, you can use an anti-join:

select uuid
from my_table a
left join my_table b on b.uuid = a.uuid and b.status = 'ok'
where status = 'error'
  and b.uuid is null

Upvotes: 1

Related Questions