Selecting Partial Duplicate Records [message #348731] |
Wed, 17 September 2008 16:15  |
aadwight
Messages: 9 Registered: September 2008
|
Junior Member |
|
|
Hi.
I have a table which contains columns W, X, Y, Z. W is the primary key. If 2 records have the same Y and Z, those 2 records are duplicates. I need to select W from all duplicate records.
This query selects the correct records:
SELECT Y, Z, COUNT(*)
FROM my_table
GROUP BY (Y, Z)
HAVING COUNT(*) > 1
However, because the results are grouped, I can't see W. Any suggestions? Thanks in advance.
-A
|
|
|
|
Re: Selecting Partial Duplicate Records [message #348736 is a reply to message #348733] |
Wed, 17 September 2008 16:27   |
aadwight
Messages: 9 Registered: September 2008
|
Junior Member |
|
|
anacedent wrote on Wed, 17 September 2008 17:20 | select a.w, b.w
from mytable a, mytable b
where ...
|
Thanks for the reply, but I'm afraid I don't understand how this answers my question. What are 'a' and 'b' in your example, and why does that query return the results I want?
|
|
|
|
Re: Selecting Partial Duplicate Records [message #348743 is a reply to message #348738] |
Wed, 17 September 2008 17:22   |
aadwight
Messages: 9 Registered: September 2008
|
Junior Member |
|
|
Unless you're talking about using code tags (which you didn't do yourself) I don't understand what posting guidelines you think I'm violating.
Quote: |
> What are 'a' and 'b' in your example,
They are called table aliases.
|
It's my understanding that the only purpose of an alias is to provide more readable code and results. I still have no idea what your code is actually supposed to accomplish.
Quote: |
>why does that query return the results I want?
Because it can & does with a correct WHERE clause
|
This makes me think that you either don't understand my question, or you just don't know what you're talking about. I'm not trying to insult you, because I appreciate that you've spent time trying to help me, but I'm currently no closer to solving my problem than I was before I made my first post.
|
|
|
|
Re: Selecting Partial Duplicate Records [message #348745 is a reply to message #348731] |
Wed, 17 September 2008 18:14   |
aadwight
Messages: 9 Registered: September 2008
|
Junior Member |
|
|
It looks to me like you're checking to see if a record is present in each of two references to the same table. This seems like it would result in every record being flagged as a duplicate. I'm not sure what I'm missing.
|
|
|
|
|
|
|
|
|
|
Re: Selecting Partial Duplicate Records [message #349012 is a reply to message #348783] |
Thu, 18 September 2008 10:00   |
aadwight
Messages: 9 Registered: September 2008
|
Junior Member |
|
|
anacedent wrote on Thu, 18 September 2008 00:24 | alternatively
SELECT A.W, B.W
FROM MY_TABLE A, MY_TABLE B
WHERE A.Y = B.Y AND A.Z = B.Z
AND A.W <> B.W AND A.W > B.W
This will actually give the pair of PK values which are duplicates Y & Z values
|
This returns exactly what I need, and now I even understand why. Thanks for your patience.
Alessandro Rossi wrote on Thu, 18 September 2008 06:39 | just use analytics...
Not tested
select *
from (
SELECT a.*,COUNT(*) over (partition by Y, Z) as cnt
FROM my_table a
)
where cnt > 1
Bye Alessandro
|
I think this works as well, but I haven't figured out why yet. I'll have to see which one runs more quickly. Thanks again for your help, everyone.
|
|
|
|
Re: Selecting Partial Duplicate Records [message #349028 is a reply to message #349023] |
Thu, 18 September 2008 10:53   |
aadwight
Messages: 9 Registered: September 2008
|
Junior Member |
|
|
anacedent wrote on Thu, 18 September 2008 11:31 |
neither of the above is/was true
you don't know, what you don't know.
|
I agree with both of these statements. I don't have much practice with SQL, and probably know twice as much today as I knew yesterday, thanks to this forum. I was unsure if you understood my question because I did not understand your answer. I appreciate your help, and I apologize if I offended.
|
|
|
Re: Selecting Partial Duplicate Records [message #349150 is a reply to message #349028] |
Fri, 19 September 2008 02:13  |
 |
Alessandro Rossi
Messages: 166 Registered: September 2008 Location: Rome
|
Senior Member |
|
|
The technique I used takes advantage of a special Oracle feature well explained here.
I don't know your understandings of SQL so I preferred to point you to a manual where things are explained in the right order.
If you need further documentation you may find here what you need.
The alternative presented by anacedent uses a standard join documented here and deeper here.
Bye Alessandro
|
|
|