Write a query to remove the duplicate

 Input:

 

Expected Output:

 

 

CREATE TABLE t_dist

(

SOURCE VARCHAR2(20 BYTE),

DESTINATION   VARCHAR2(20 BYTE),

DISTANCE    NUMBER

);

 

Insert into t_dist  values ('DUBAI','INDIA',2475);

Insert into t_dist  values ('INDIA','DUBAI',2475);

Insert into t_dist values ('INDIA','QATAR',2885);

Insert into t_dist values ('QATAR','INDIA',2885);

Insert into t_dist values ('INDIA','SAUDI',3496);

COMMIT;

 

select * from t_dist;

Step1:

SELECT d.*,

row_number() over (partition BY LEAST(SOURCE, DESTINATION),

GREATEST(SOURCE, DESTINATION), distance order by distance )AS RNUM

FROM t_dist d;

DUBAI   INDIA   2475    1

INDIA   DUBAI   2475    2

INDIA   QATAR   2885    1

QATAR   INDIA   2885    2

INDIA   SAUDI   3496    1

Step2:

WITH t AS

(SELECT d.*,

row_number() over (partition BY LEAST(SOURCE, DESTINATION),

GREATEST(SOURCE, DESTINATION), distance order by distance )AS RNUM

FROM t_dist d

)

SELECT * FROM t WHERE RNUM =1;

DUBAI   INDIA   2475    1

INDIA   QATAR   2885    1

INDIA   SAUDI   3496    1