How to select single row when there are multiple rows with same meaning?-CodePudding

I was trying to fetch single records for the multiple rows where the business logic is same.

Sample Data:

I was trying to fetch this output:

For an example "Kolkata to Delhi" and "Delhi to Kolkata" have the same business meaning.

How can I fetch one record for "Kolkata to Delhi" and "Delhi to Kolkata" and same for the rest?

I am using Oracle 12c for this.

CodePudding user response：

Use DISTINCT and the LEAST and GREATEST functions:

SELECT DISTINCT
       LEAST(source, destination) AS source,
       GREATEST(source, destination) AS destination
FROM   table_name

Which, for the sample data:

CREATE TABLE table_name (source, destination, distance) AS
SELECT 'A', 'B', 100 FROM DUAL UNION ALL
SELECT 'A', 'B', 110 FROM DUAL UNION ALL
SELECT 'B', 'A', 120 FROM DUAL UNION ALL
SELECT 'C', 'D', 200 FROM DUAL UNION ALL
SELECT 'D', 'C', 290 FROM DUAL UNION ALL
SELECT 'E', 'F', 310 FROM DUAL UNION ALL
SELECT 'F', 'E', 320 FROM DUAL UNION ALL
SELECT 'F', 'E', 300 FROM DUAL;

Outputs:

SOURCE DESTINATION

A B

C D

E F

SOURCE	DESTINATION
A	B
C	D
E	F

db<>fiddle here