SQL Server – Select Records with Different Values for a Column

group byqueryselectsql server

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

Best Answer

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

Related Solutions

MySQL – SELECT and UPDATE in a Single Query for Duplicate Records

Since there is a unique constraint/index on number, you can use ON DUPLICATE KEY UPDATE syntax - which can be combined with INSERT ... VALUES so all the rows are inserted/updated in one statement. The additional requirement that new null values do not update existing ones can be satisfied using COALESCE() function. Example:

INSERT INTO Contacts
    (number, name, city)
VALUES
    (1234567892, 'Earl Grey', 'Salem'),
    (3333333333, 'Rich Malfoy', 'Sacramento'),
    (7656453248, 'Bill Gates', 'New York'),
    (5555555555, 'James McGill', 'Boston'),
    (6666666666, NULL, 'Baton Rouge')
ON DUPLICATE KEY UPDATE
    name = COALESCE(VALUES(name), name),
    city = COALESCE(VALUES(city), city) ;

Test at SQLfiddle

SQL Select – How to Select Records with Multiple Values for a Column

You could do something like:

select distinct x.id, x.lastname, x.firstname, x.email
from t as x
join (
    select id
    from t
    group by id
    having count(distinct email) > 1
) as y
    on x.id = y.Id

Best Answer

Related Solutions

MySQL – SELECT and UPDATE in a Single Query for Duplicate Records

SQL Select – How to Select Records with Multiple Values for a Column

Related Question