CODEWITHSUNDEEP
sqlselect
Ice
Ice

Reputation: 469

SQL: Find multiple client numbers for same person

How can I find if a name is assigned different client number? For example, how can I find Jude in the dataset was assigned three different client numbers? I've tried to use group by to find the duplicate client numbers.

ID   CLIENT_NUMBER  NAME  START_DT       END_DT
 1      1           Jude   2017-09-13    NULL
 2      2           Jude   2017-09-19    2017-09-26
 3      3           Jude   2017-09-26    null

Upvotes: 1

Views: 531

Answers (2)

Vash
Vash

Reputation: 1787

Try: 

SELECT NAME
FROM
YOUR_TABLE
GROUP BY NAME
HAVING COUNT(DISTINCT CLIENT_NUMBER) > 1;

Upvotes: 1

Mureinik
Mureinik

Reputation: 311978

You could count how many distinct client_numbers each name has:

SELECT   name
FROM     mytable
GROUP BY name
HAVING   COUNT(DISTINCT client_name) > 1

Upvotes: 2

Related Questions