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
Improve this question

1 Answer 1

Reset to default
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

Improve this answer

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.