Skip to Main Content
  • Questions
  • Find all routes possible from the given source and destination values in table

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question.

Asked: May 25, 2017 - 10:41 am UTC

Last updated: May 25, 2017 - 2:23 pm UTC

Version: Oracle12c

Viewed 1000+ times

You Asked

I have a table containing columns(source , destination).Suppose it has values as given below:
select * from t;

Source Destination
---------- -----------------------
1 Pune Mumbai
2 Mumbai Pune
3 Nashik Goa
4 Goa Nashik

It should print route as only one of the two(1 or 2 , 3 or 4) entry as both the entries covering same route.
I am not able to figure out how to distinguish the 1st from 2nd record so that I can select only one of them.
I need solution in PLSQL query.
I am using Oracle12c.

Required Output:

Routes
--------
Pune-Mumbai
Nashik-Goa

and Chris said...

Pass both columns to least and greatest. Then return the distinct of these:

CREATE TABLE t (
  id int, src varchar2(6), dst varchar2(6)
);
    
INSERT INTO t VALUES (1, 'Pune', 'Mumbai');
INSERT INTO t VALUES (2, 'Mumbai', 'Pune');
INSERT INTO t VALUES (3, 'Nashik', 'Goa');
INSERT INTO t VALUES (4, 'Goa', 'Nashik');
commit;

select * from t;

ID  SRC     DST     
1   Pune    Mumbai  
2   Mumbai  Pune    
3   Nashik  Goa     
4   Goa     Nashik

select distinct least(src, dst) || '-' || greatest(src, dst) from t;

LEAST(SRC,DST)||'-'||GREATEST(SRC,DST)  
Mumbai-Pune                             
Goa-Nashik  


Is this answer out of date? If it is, please let us know via a Comment