Question 328958 in PostgreSQL Question posted in
The official documentation can be found here.

PostgreSQL select unique rows based on any combinations of 2 cols

I have a table like below:

city1 city2 dist
New York Berlin 7900
Berlin New York 7900
Oregon Ohio 5700
Montreal Rio 5700
Ohio Oregon 5700
Rio Montreal 5700
Moscow Tokyo 4200
Tokyo Moscow 4200

Both row 1 and row 2 are same things and the only difference is in the first one Berlin is city2 and in the second one Berlin is city1.
I want to show this kind of rows only once…

How to do it?

Tags:

4

Answers


  1. 0

    You can do:

    select a.*
from t a
left join t b on b.city1 = a.city2 and b.city2 = a.city1
where b.city1 is null or b.city1 > b.city2

    I added a couple of unmatched edge cases in the demo fiddle.

    Login or Signup to reply.
  2. 0

    Use a CASE statement to ensure that the smaller of the two city names always appears first.I mean, "a" is minimal and Z is maximal.s minimal and Z is maximal. This way, each pair will have a unique representation.
    Then，group the data by the newly ordered city pairs and then filter out duplicates.

    Login or Signup to reply.
  3. 0

    You can self LEFT JOIN and see if any matches are there

    Depending on th last OR condition you can choose which cite are shown in the result set

    SELECT t1.*
FROM t t1
LEFT JOIN t t2 ON t1.city1 = t2.city2 and t2.city2 = t1.city1 AND t1.dist = t2.dist
WHERE t2.city1 IS NULL 
  OR t1.city1 > t1.city2
    city1 city2 dist
    Conce Santiago 500
    New York Berlin 7900
    Oregon Ohio 5700
    Rio Montreal 5700
    Talca Linares 80
    Tokyo Moscow 4200 
    SELECT 6

    SELECT t1.*
FROM t t1
LEFT JOIN t t2 ON t1.city1 = t2.city2 and t2.city2 = t1.city1 AND t1.dist = t2.dist
WHERE t2.city1 IS NULL 
  OR t2.city1 > t2.city2
    city1 city2 dist
    Berlin New York 7900
    Conce Santiago 500
    Montreal Rio 5700
    Moscow Tokyo 4200
    Ohio Oregon 5700
    Talca Linares 80 
    SELECT 6

    fiddle

    Login or Signup to reply.
  4. 0

    Here’s the simplest query that works:

    SELECT * FROM t WHERE city1 < city2

    The joins are unnecessary.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search