All Forums General
sam_kum_1 4 posts Joined 05/15
14 May 2015
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.
 

 
 
 
 

 
 

 
 
 

dnoeth 4628 posts Joined 11/04
14 May 2015

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? 

Dieter

CarlosAL 512 posts Joined 04/08
14 May 2015

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.

yuvaevergreen 93 posts Joined 07/09
15 May 2015

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

sam_kum_1 4 posts Joined 05/15
18 May 2015

Thanks Dieter the query worked fine.

You must sign in to leave a comment.