 Challenging Query [message #641561] |
Tue, 18 August 2015 03:10  |
rakeshramm
Messages: 175
Registered: September 2006
Location: Oracle4u.com
|
Senior Member |
|
|
Want to write a query to avoid the logical duplicate records .(Technically these records are not duplicate ie:
we can avoid records using the distinct keyword)
create table LOGICAL_DUP
( source VARCHAR2(100),
destination VARCHAR2(100),
distance NUMBER
) ;
insert into LOGICAL_DUP (SOURCE, DESTINATION, DISTANCE) values ('AA', 'BB', 5000);
insert into LOGICAL_DUP (SOURCE, DESTINATION, DISTANCE) values ('CC', 'DD', 1000);
insert into LOGICAL_DUP (SOURCE, DESTINATION, DISTANCE) values ('FF', 'GG', 1500);
insert into LOGICAL_DUP (SOURCE, DESTINATION, DISTANCE) values ('AA', 'CC', 3000);
insert into LOGICAL_DUP (SOURCE, DESTINATION, DISTANCE) values ('GG', 'FF', 1500);
insert into LOGICAL_DUP (SOURCE, DESTINATION, DISTANCE) values ('BB', 'AA', 5000);
commit;
Output required is
AA BB 5000
AA CC 3000
CC DD 1000
FF GG 1500
|
|
|
|
| Re: Challenging Query [message #641565 is a reply to message #641561] |
Tue, 18 August 2015 04:14  |
 |
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Quote: avoid the logical duplicate records
Define duplicate. There is no duplicates in your example.
Quote:Output required is
Explain the output. What are the rules that lead to it?
What is challenging here is to have a clear specification.
[Updated on: Tue, 18 August 2015 04:14] Report message to a moderator |
|
|
|
|
|
| Re: Challenging Query [message #641567 is a reply to message #641561] |
Tue, 18 August 2015 04:49 |
bugfox
Messages: 18
Registered: October 2010
|
Junior Member |
|
|
select source,
destination,
distance
from (select source,
destination,
distance,
row_number() over(partition by greatest(source, destination), least(source, destination) order by source, destination) rn
from logical_dup)
where rn = 1
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
