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