Oracle hierarchy queries

Question

I have next table and I need to write hierarchy queries:

1.What city we can get by 2 steps.

2.Find the lowest route between two cities.

+-------------+-------------+----------+
|    City1    |    City2    | Distance |
+-------------+-------------+----------+
| Leningrad   | Vladivostok |      234 |
| Vladivostok | Viborg      |      423 |
| Viborg      | Novgograd   |       92 |
| Novgograd   | Tula        |      158 |
| Leningrad   | Tula        |      321 |
| Tula        | Moscow      |      111 |
| Moscow      | Novgograd   |      421 |
| Leningrad   | Moscow      |      244 |
+-------------+-------------+----------+

I am stuck with something like that

WITH stepbystep ( city1, city2, distance ) AS (
  SELECT city1, city2 || '-' || city1, distance
  FROM   route
  WHERE  city1 = 'Moscow'
     UNION ALL
  SELECT r.city1
       , s.city2 || '-' || r.city1
       , r.distance + s.distance
  FROM route r
       INNER JOIN
       stepbystep s
       ON ( s.city1 = r.city2 )
  )
SELECT city2, distance FROM stepbystep

Answer 1

To get cities in 2 steps, You may try below query -

stepbystep ( city1, city2, distance, lvl ) AS (
  SELECT city1, city1 || '-' || city2, distance, 1 lvl
  FROM   route
  --WHERE  city1 = 'Moscow'
     UNION ALL
  SELECT r.city1
       , r.city1 || '-' || s.city2
       , r.distance + s.distance
       , s.lvl+1
  FROM route r
       INNER JOIN
       stepbystep s
       ON ( s.city1 = r.city2 )
  WHERE s.lvl <= 2
  )
SELECT city2, distance FROM stepbystep
WHERE lvl = 2              -- Using lvl =2 to get the cities in 2 steps.

Here is the fiddle.

For shortest path you try below query -

stepbystep ( city1, city2, distance, lvl ) AS (
  SELECT city1, city1 || '-' || city2, distance, 1 lvl
  FROM   route
  --WHERE  city1 = 'Moscow'
     UNION ALL
  SELECT r.city1
       , r.city1 || '-' || s.city2
       , r.distance + s.distance
       , s.lvl+1
  FROM route r
       INNER JOIN
       stepbystep s
       ON ( s.city1 = r.city2 )
  WHERE s.lvl <= 2
  )
select city1
      ,substr(city2, instr(city2, '-', -1)++1, length(city2)) dest
      ,distance
from (select city2
            ,city1
            ,substr(city2, instr(city2, '-', -1)++1, length(city2)) dest
            ,distance
            ,rank() over(partition by city1, substr(city2, instr(city2, '-', -1)++1, length(city2)) order by distance) rnk
      from stepbystep)
where rnk = 1

Here is the demo.