Removing Logical Duplicates

General

Removing Logical Duplicates

Hi All,

Consider the below scenario

LOC_1    LOC_2   Distance

France   Spain       1000

Ohio      Newyork   3000

Spain    France       1000

Paris    Finland        5000

Paris    Turkey         7000

Newyork  Ohio     3000

Finland  Paris       5000

Turkey  Paris         7000

If we see the above data , it logically duplicate in nature as any source to destination will be sufficient.

My output Should be 

LOC_1    LOC_2   Distance

France   Spain       1000

Ohio      Newyork   3000

Paris    Finland        5000                    

Paris    Turkey         7000

OR 

LOC_1    LOC_2   Distance

Spain    France       1000

Newyork  Ohio     3000

Finland  Paris       5000

Turkey  Paris         7000

Have triesd to resolve above by using the Analytical functions but  could'nt figure it out .

Kindly help.

4 REPLIES
N/A

Re: Removing Logical Duplicates

What if there's a no flight back?

This will return the correct result:

SELECT
LOC_1,
LOC_2,
distance
FROM tab
QUALIFY
ROW_NUMBER() OVER (PARTITION BY
CASE WHEN LOC_2 < LOC_1 THEN LOC_2 ELSE LOC_1 END,
CASE WHEN LOC_2 < LOC_1 THEN LOC_1 ELSE LOC_2 END
ORDER BY LOC_2) = 1

What if the distance is not the same for both rows? 

Re: Removing Logical Duplicates

Hi.

1.- Paris is in France, isn't it? ;-)

2.- Distance between countries is a blurry concept, IMHO ;-)

3.- Sounds like a homework... ;-)

Cheers.

Carlos.

Re: Removing Logical Duplicates

IF its TD14, greatest and least functions can also be used.

Thanks,

Yuva

Re: Removing Logical Duplicates

Thanks Dieter the query worked fine.