Wednesday, 11 September 2013

SELECT Only Records With Duplicate (Column A || Column B) But Different (Column C) Values

SELECT Only Records With Duplicate (Column A || Column B) But Different
(Column C) Values

I apologize for the confusing title, I can't figure out the proper wording
for this question. Instead, I'll just give you the background info and the
goal:
This is in a table where a person may or may not have multiple rows of
data, and those rows may contain the same value for the activity_id, or
may not. Each row has an auto-incremented ID. The people do not have a
unique identifier attached to their names, so we can only use
first_name/last_name to identify a person.
I need to be able to find the people that have multiple rows in this
table, but only the ones who have multiple rows that contain more than one
different activity_id.
Here's a sample of the data we're looking through:
unique_id | first_name | last_name | activity_id
---------------------------------------------------------------
1 | ted | stevens | 544
2 | ted | stevens | 544
3 | ted | stevens | 545
4 | ted | stevens | 546
5 | rachel | jameson | 633
6 | jennifer | tyler | 644
7 | jennifer | tyler | 655
8 | jennifer | tyler | 655
9 | jack | fillion | 544
10 | mallory | taylor | 633
11 | mallory | taylor | 633
From that small sample, here are the records I would want returned:
unique_id | first_name | last_name | activity_id
---------------------------------------------------------------
dontcare | ted | stevens | 544
dontcare | jennifer | tyler | 655
Note that which value of unique_id gets returned is irrelvant, as long as
it's one of the unique_ids belonging to that person, and as long as only
one record is returned for that person.
Can anyone figure out how to write a query like this? I don't care what
version of SQL you use, I can probably translate it into Oracle if it's
somehow different.

No comments:

Post a Comment