Help with SQL - Find differant Last Names by SSN

Hi.. I have data like this.  I need to find all the records that have differant Last Names by SSN

SSN              LAST
 1                 Jones
 1                 jones
 1                brown

 2                green
2                 green

3                 black
3                 white
3                 black

The result should be

SSN
1
3


thanks
LVL 1
JElsterAsked:
Who is Participating?
 
Kevin CrossConnect With a Mentor Chief Technology OfficerCommented:
Do you consider the 'jones' and 'Jones' different entries?  In other words, if you had 'Green' and 'green', would 2 also show up in the results?

Anyway, you can start with something like this.
SELECT SSN
FROM your_table_name
GROUP BY SSN
HAVING COUNT(DISTINCT LAST) > 1
;

Open in new window


There are some other solutions, but this one should work.  If the answer to the question above is the rows with case differences should be treated the same, just make sure your collation is case insensitive.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.