General
Fan

## 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
Junior Contributor

## Re: Removing Logical Duplicates

What if there's a no flight back?

This will return the correct result:

`SELECT  LOC_1,  LOC_2,  distanceFROM tabQUALIFY  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?

Junior Supporter

## 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.

Enthusiast

## Re: Removing Logical Duplicates

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

`Thanks,`

`Yuva`

Fan

## Re: Removing Logical Duplicates

Thanks Dieter the query worked fine.