November 8, 2007 at 4:27 am
Hi, I'm trying to retrieve rows from one table where the key is the same but the name is different ie
key column name column
1 simon
1 simon
2 aaron
2 aaron
3 alan
4 steve
4 steven
So in the case above I want to only pull out the last two rows as although the key is the same the name is different. NB. There is no primary key on this table.
thanks
November 8, 2007 at 5:22 am
do a self join
select t1.key, t1.name, t2.name from mytable t1 join mytable t2 on t1.key=t2.key
where t1.name<t2.name -- to avoid duplicates
(the < is used instead of != to avoid duplicates)
Regards,
Andras
November 8, 2007 at 6:52 am
Hi Andras, thanks for your input. Am testing this now as I'm not 100% sure if its giving quite the correct result (as theres other things I need to consider!?).
Thanks again.
November 8, 2007 at 8:21 am
The one gotcha you might need to think about is that there's no handling of NULL. So - if one of the name fields were NULL - you wouldn't find them....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 8, 2007 at 8:23 am
What's incorrect?
If the first column is a key then you shouldn't have duplicates.
You could qualify Andras' solution by first only grabbing dups.
select t1.key, t1.name, t2.name
from ( select t3.key, t3.name, count(t3.key)
from mytable t3
group by t3.key, t3.name
having count(t3.key) > 1
)t1
join mytable t2 on t1.key=t2.key
where t1.name<t2.name -- to avoid duplicates
November 8, 2007 at 8:43 am
Thanks for that guys. The first column isn't a key its just called key, like I say there is no PK on the table. I think this has helped answer my question now though 🙂
November 8, 2007 at 11:07 am
Glad to help and let us know if you figure something else out. Might help the next DBA.
Steve
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply