get unique available combination-CodePudding

I'm trying to solve below problem, where I need to get available unique combination in data set

Here is input table having two columns

IND, USA
USA, IND
AUS, IND
IND, AUS
UAE, AUS

used this query

SELECT DISTINCT
       LEAST(c1, c2) AS c1,
       GREATEST(c1, c2) AS c2
FROM   table_name

Got output

IND, USA
AUS, IND
AUS, UAE

first two records in output are fine, but third one is not correct because AUS, UAE combination dont even exists in input data.

May I know how to fix this in my query ?

expectedoutput

IND, USA
AUS, IND
UAE, AUS

CodePudding user response：

Using ROW_NUMBER():

WITH cte AS (
    SELECT t.*, ROW_NUMBER() OVER (PARTITION BY LEAST(c1, c2), GREATEST(c1, c2)
                                   ORDER BY c1) rn
    FROM table_name t
)

SELECT c1, c2
FROM cte
WHERE rn = 1;

The strategy here is to assign a row number to each group of records having the same pair of country values. We report the single pair having the "earlier" first country.

CodePudding user response：

A union of an intercept with a reverse except can get you that.

create table table_name (c1 char(3), c2 char(3));
insert into table_name values
  ('IND' , 'USA') 
, ('USA' , 'IND') 
, ('AUS' , 'IND') 
, ('IND' , 'AUS') 
, ('UAE' , 'AUS');

(SELECT DISTINCT 
  LEAST(c1, c2) AS c1, 
  GREATEST(c1, c2) AS c2
FROM table_name 
intersect
SELECT c1, c2 from table_name) 
union
(SELECT DISTINCT 
  GREATEST(c1, c2) AS c1, 
  LEAST(c1, c2) AS c2
FROM table_name 
except
SELECT c2, c1 from table_name)
ORDER BY c2 DESC

c1	c2
IND	USA
AUS	IND
UAE	AUS

db<>fiddle here