Select only records that have different values for a particular column

Question

In my table there are some records that have different name values for the same account number and I want to extract only the records that have different values in the name field, but that share the same account number. Records that always have the same name associated with the account number I want to ignore, regardless of how many times they occur.

So, from this :

ID NAME ACCNR
1 Peter 123456
2 Peter 123456
3 Jimmy 445566 
4 John 987654 
5 Robert 987654 
6 Robert 556644 
7 Sally 112233
8 Harry 987654

I would like to extract this :

4 John 987654 
5 Robert 987654 
8 Harry 987654

Answer 1

SELECT DISTINCT t1.*
FROM table t1, table t2
WHERE t1.accnr = t2.accnr
 AND t1.name != t2.name

• Tesla: Why do you have >= ? It will just return everything.
• Akina: @Tesla You're absolutely right, great thanks. Solution corrected.

Answer 2

Looks like I achieved what I needed with :

SELECT NAME,ACCNR FROM tblAccounts WHERE (ACCNR) in (
SELECT ACCNR FROM tblAccounts
GROUP BY ACCNR
HAVING min(NAME) <> max(NAME))

• Lennart - Slava Ukraini: You will get duplicates in the result if you add a row like: insert into tblAccounts (id, name, accnr) values (9,'Pete', 123456) . Is that ok or do you want to filter out one of the Peter rows?
• Tesla: Yes, thank you. That is correct. I am fine with that. I am sure if needed one could just encapsulate the query with a distinct to filter them out.

Answer 3

A slight variation of your solution using window functions:

select id, name, accnr 
from (
 select id, name, accnr
 , max(name) over (partition by accnr) as mx
 , min(name) over (partition by accnr) as mn 
 from tblaccounts
) 
where mx <> mn;

One benefit is that you can return id as requested in your question. This will also result in duplicate Peter's