Querying SQL Server table with different values in same column with same ID

Question

I have an SQL Server 2012 table with ID, First Name and Last name. The ID is unique per person but due to an error in the historical feed, different people were assigned the same id.

------------------------------
ID  FirstName   LastName
------------------------------   
1    ABC         M
1    ABC         M
1    ABC         M
1    ABC         N
2    BCD         S
3    CDE         T
4    DEF         T
4    DEF         T

There are two ID's which are present multiple time. 1 and 4. The rows with id 4 are identical. I dont want this in my result. The rows with ID 1, although the first name is same, the last name is different for 1 row. I want only those ID's whose ID is same but one of the first or last names is different.

I tried loading ID's which have multiple occurrences into a temp table and tried to compare it against the parent table albeit unsuccessfully. Any other ideas that I can try and implement?

This is the output I am looking for

ID
---
1

Answer 1

If you want the ids, then use aggregation and having:

select id
from t
group by id
having min(firstname) <> max(firstname) or min(lastname) <> max(lastname);

Answer 2

Try This:

CREATE TABLE #myTable(id INT, firstname VARCHAR(50), lastname VARCHAR(50))

INSERT INTO #myTable VALUES 
(1,    'ABC',         'M'),
(1,    'ABC',         'M'),
(1,    'ABC',         'M'),
(1,    'ABC',         'N'),
(2,    'BCD',         'S'),
(3,    'CDE',         'T'),
(4,    'DEF',         'T'),
(4,    'DEF',         'T')

SELECT id FROM (
    SELECT DISTINCT id, firstname, lastname
    FROM #myTable) t GROUP BY id HAVING COUNT(*)>1

OUTPUT is : 1