CODEWITHSUNDEEP
sqldatabaseoracle-databaseoracle-sqldeveloperoracle18c
NIkhil Rohilla
NIkhil Rohilla

Reputation: 37

How to write a query to find a record which is not processed

I am trying to write a query from a table A which has 2 columns :

ID , STATUS

The Status can be PROCESSING, NOTPROCESSED, FAILED, SUCCESS

When a record is successfully processed, a new record is created in the DB with STATUS as PROCESSED and the ID is the same as the previous NOTPROCESSED record.

The Sample Records in DB would like :

1      NOTPROCESSED
2      PROCESSED
1      PROCESSED
3      NOTPROCESSED
4      NOTPROCESSED
2      PROCESSED
3      NOTPROCESSED
4      NOTPROCESSED

The records can appear as duplicate for NOTPROCESSED.

I have to query the records which are NOTPROCESSED i.e

3      NOTPROCESSED
4      NOTPROCESSED

Its getting quite confusing to write the query.

Can anyone help with the logic.

Upvotes: 0

Views: 257

Answers (4)

Popeye
Popeye

Reputation: 35900

You can use analytical function as follows:

select * from
(select t.*, count(case when status = 'PROCESSED' then 1 end)
                   over (partition by ID) as cnt
from your_table t) t
where status = 'NOTPROCESSED' and cnt = 0

Upvotes: 1

juergen d
juergen d

Reputation: 204756

Group by the ids and take only those groups having no record of status PROCESSED

select id
from your_table
group by id
having sum(case when status = 'PROCESSED' then 1 else 0 end) = 0

or get only the ones with only one kind of status

having count(distinct status) = 1

or use alphabetically the highest status

having max(status) = 'NOTPROCESSED'

Upvotes: 2

George Joseph
George Joseph

Reputation: 5922

you may use not exists to get this output.

select distinct a.id,a.status
  from table a
 where a.status='NOTPROCESSED'
   and not exists (select null
                     from table b
                    where b.id=a.id
                      and b.status='PROCESSED')

Upvotes: 4

user9706
user9706

Reputation:

Here are a couple of options:

select distinct id from A where id not in (
  select id from A where status = 'PROCESSED'
);

select distinct id from A natural left join (
  select id from A where status = 'PROCESSED'
) as B where B.id is null;

Upvotes: 1

Related Questions