Self JOIN on cities-CodePudding

As you can see the cities for rows 1,2 are the same but in different positions and the distance is the same. The setup for rows 3,4 are set up the same way.

I'm trying to get a self JOIN working to reduce the output to 2 rows but I'm running into a syntax error, which I could use some help with. Any help would be appreciated.

Below is my attempt


with src_dest_distance (src, destination,  distance) AS 
   (
     select 'NYC', 'ALBANY', 200 from dual union all
     select  'ALBANY', 'NYC', 200 from dual union all
   select 'Trenton', 'Wildwood', 100 from dual union all
     select  'Wildwood', 'Trenton', 100 from dual
)
WITH CTE as
   (SELECT *, row_number over() as id
    from src_dest_distance)
SELECT 
              t1.src,
              t1.destination, 
              t1.distance
FROM CTE t1
JOIN CTE t2
ON t1.src = t2.destination 
AND t1.id < t2.id

CodePudding user response：

Of course, you can fix it:

with src_dest_distance (src, destination,  distance) AS 
   (
     select 'NYC', 'ALBANY', 200 from dual union all
     select  'ALBANY', 'NYC', 200 from dual union all
     select 'Trenton', 'Wildwood', 100 from dual union all
     select  'Wildwood', 'Trenton', 100 from dual
)
, CTE as
   (SELECT d.*, row_number() over(order by 1) as id
    from src_dest_distance d)
SELECT 
              t1.src,
              t1.destination, 
              t1.distance
FROM CTE t1
JOIN CTE t2
ON t1.src = t2.destination 
AND t1.id < t2.id

But I would suggest to make it easier:

with src_dest_distance (src, destination,  distance) AS 
   (
     select 'NYC', 'ALBANY', 200 from dual union all
     select  'ALBANY', 'NYC', 200 from dual union all
     select 'Trenton', 'Wildwood', 100 from dual union all
     select  'Wildwood', 'Trenton', 100 from dual
)
select 
  distinct 
    greatest(src, destination) src
   ,least   (src, destination) destination
   ,distance
from src_dest_distance