CODEWITHSUNDEEP
mysqlsql
theamateurdataanalyst
theamateurdataanalyst

Reputation: 2834

MySQL query with distinct pairs of values

Suppose I have the following table

  ID1 ID2
1   a   b
2   b   a
3   c   d

I am trying to write a query where rows like 1 and 2 are returns because they are essentially the same just reversed by column, so I wrote the following query:

SELECT * 
FROM Likes A, Likes B
WHERE  A.ID2 = B.ID1 AND B.ID2 = A.ID1

This returns

  ID1 ID2
1   a   b
2   b   a

The next step for me is to return only the first one i.e. return only distinct rows where where A.ID2 = B.ID1 AND B.ID2 = A.ID1.

Upvotes: 1

Views: 174

Answers (1)

pala_
pala_

Reputation: 9010

You can introduce a range comparison in the join criteria to limit it to just one row.

select l1.*
  from likes l1
    inner join likes l2
      on l1.id1 = l2.id2 
        and l1.id2 = l2.id1 
        and l1.id1 < l2.id1

This will select the row with the alphabetically lowest id1. Unless you need some other criteria for choosing which row to return?

Upvotes: 1

Related Questions