Home » SQL & PL/SQL » SQL & PL/SQL » How to delete duplicate rows with reverse data (Oracle 9i)
How to delete duplicate rows with reverse data [message #386004] Thu, 12 February 2009 03:43 Go to next message
parag_narkhede
Messages: 110
Registered: January 2008
Location: Pune
Senior Member
Hello Everyone,

I have following requirement.
There is one table with following data

Column1 Column2
-----------------------
X Y
Y X
P Q
Q P

I want to select all data from table but it should not select duplicate rows
EX: Out of first 2 rows, it should select (X,Y) combination. It should not select 2nd row.

Re: How to delete duplicate rows with reverse data [message #386006 is a reply to message #386004] Thu, 12 February 2009 03:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Which one?
Why (X,Y) and not (Y,X)?
Is (X,Y) the same thing than (Y,X)?
If yes, if there is only (Y,X) can you display (X,Y)?
...
Many questions before having a complete specification.

Regards
Michel

[Updated on: Thu, 12 February 2009 03:51]

Report message to a moderator

Re: How to delete duplicate rows with reverse data [message #386012 is a reply to message #386004] Thu, 12 February 2009 04:05 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
We've had this question before - as I remember, the simplest 2 column solution was:
with src as (select 'X' col_1, 'Y' col_2 from dual union all
             select 'Y', 'X' from dual union all
             select 'P', 'Q' from dual union all
             select 'Q', 'P' from dual)
select distinct least(col_1,col_2),greatest(col_1,col_2)
from   src;


For more than two columns, you'll have to write a function to sort the columns into a defined order and fetch distinct values of that.
Re: How to delete duplicate rows with reverse data [message #386014 is a reply to message #386012] Thu, 12 February 2009 04:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This what I had in mind but it depends on the answers of my questions.

Regards
Michel
Re: How to delete duplicate rows with reverse data [message #386058 is a reply to message #386004] Thu, 12 February 2009 06:56 Go to previous messageGo to next message
parag_narkhede
Messages: 110
Registered: January 2008
Location: Pune
Senior Member
Thanks to all of you.

Michel, Reason is: I want to send all data from this table to another table. but if i send both (X,Y) and (Y,X) combinations then my business rule will fail.
Thats why I want only one combination.

JRowbottom, Thanks for your reply.
I tried to use WITH clause before as well. But i get error "SQL statement doesn't return rows". I tried on 9i DB and 10g DB both.
Also i am not able to understand the query which you have suggested.
Re: How to delete duplicate rows with reverse data [message #386060 is a reply to message #386058] Thu, 12 February 2009 07:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Michel, Reason is: I want to send all data from this table to another table. but if i send both (X,Y) and (Y,X) combinations then my business rule will fail.
Thats why I want only one combination.

This was not my questions. I don't ask for the reason.

Quote:
tried to use WITH clause before as well. But i get error "SQL statement doesn't return rows". I tried on 9i DB and 10g DB both.

Copy and paste what you tried otherwise how could we what you are doing wrong?

Quote:
Also i am not able to understand the query which you have suggested.

Which part don't you understand?

Regards
Michel
Re: How to delete duplicate rows with reverse data [message #386061 is a reply to message #386004] Thu, 12 February 2009 07:15 Go to previous message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
You should also realise that JRowbottom is using the with clause to synthesise some test data due to the lack of a table to test on.
You shouldn't need to use a with clause in your solution.
Previous Topic: how to write this query using correlation subquery or non exists clause
Next Topic: Query to show both tables & sequences
Goto Forum:
  


Current Time: Fri Dec 09 02:06:19 CST 2016

Total time taken to generate the page: 0.14043 seconds