CODEWITHSUNDEEP
sqlselectdb2
Petras Paludnevičius
Petras Paludnevičius

Reputation: 29

SQL DB2 Select filtering values with Same Id and several rows

my problem looks like this. I have a large table with 1M+ records. It looks something like this

ID STEP_ID
1    abc
1    dce
1    bbv
2    abc
2    ddb
3    bbv
4    asd

The thing is I want to write select query that would exclude all IDs if step_id is equal to abc. So it would look like

ID STEP_ID
3    bbv
4    asd

enter image description hereenter image description here

Upvotes: 1

Views: 316

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

Use not exists:

select t.*
from t
where not exists (select 1 from t t2 where t2.id = t.id and t2.step_id = 'abc');

For performance, you want an index on (id, step_id) -- both columns, in that order.

If you cannot create an index, it might be faster to use window functions:

select t.*
from (select t.*, 
             sum(case when step_id = 'abc' then 1 else 0 end) over (partition by id) as num_abc
      from t
     ) t
where num_abc = 0;

Upvotes: 1

GMB
GMB

Reputation: 222432

You can use not exists:

select t.*
from mytable t
where not exists (select 1 from mytable t1 where t1.id = t.id and t1.step_id = 'abc')

For performance, consider an index on (id, step_id).

Upvotes: 1

Related Questions