I am attempting to write a query that will provide me with IDs when they're duplicated (caused by different Type values) and Code values do NOT match in the duplicated rows. Only 2 Type values are possible, so there will only ever be 2 duplicates.

For example:

My desired data will not include ID 1 since its Codes are the same.

I am unsure of the syntax and logic necessary to only return IDs whose Codes do not match, like ID 2 in my example. I will also not be including the Type column in the final report, as the information required exists in the ID column. I'm using DISTINCT as I don't need the duplicate row either. The single ID is perfectly fine, as long as its invisible mate has a different Code. Here is the basis of my query:

SELECT DISTINCT id, code
FROM table
WHERE id LIKE 'ID%'
AND

This is where I am. Obviously, it's not much to go on. I won't be sorry for asking questions and learning and I certainly will accept any and all feedback on this issue.

I am also open to alternate suggestions, like "just do it in Excel after you pull ALL the data - it will be faster," but you have to explain yourself ;) Thank you!

CodePudding user response：

Aggregate by ID and check whether you get more than one code (by comparing min and max or by counting distinct):

select id, min(code), max(code)
from mytable
group by id
having min(code) <> max(code)
order by id;

CodePudding user response：

rank ID based on code so for same code both rows of ID will be ranked 1 and if code is different then row for particular ID will be ranked 1 and 2. Later we can just filter ID's with rank 2 to get desired result.

with temp as(
 Select 
  ID, 
  rank() over (partition by ID order by code) as rn 
 from table_name)

select ID from temp where rn = 2