0

I am attempting to pull ids of records where attribute a is the same, but attribute b has at least 1 record that does not have a match. The db has >1million records so the statement cannot be absolute but has to change with the data.

i.e in the below I want the result to return id 1, 2 and 3 because a is equal and b is not equal in all instances, and return id 4 and 5

ID   A   B

1    y   t
2    y   m
3    y   t
4    z   r
5    z   f
6    q   c

1 Answer 1

0

This could be a way:

select
    ID
from
    t t1
where exists (select 1
              from   t t2
              where  t2.A = t1.A
                     and t2.B <> t1.B)
order by
    ID;

It could be read as: Give me all ID's if exists another row with same A and different B.

| id |
| -: |
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |

db<>fiddle here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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