How to find all rows that have same values but possibly permuted rows in SQL

2143 views sql

Cannot figure our how to count all rows that have same values in any order. E.g in this example

1| Jay   | Jo
2| Jay   | Jo
3| Jay   | Jo
4| Jo    | Jay
5| Linda | Luke
6| Linda | Luke
7| Luke  | Linda
8| Luke  | Wendy

I am looking for the following output:

Jay   | Jo    | 4
Linda | Luke  | 3
Luke  | Wendy | 1

I was killing myself over it for 2 days. Everything does not work. Joins lead to overcount, unions don't work as well. since they preserve entries with both orders ... I understand that I probably need to do some sort of self join and to consider two copies of the same table. But what manipulations to perform, I don't know! Really confused! Please help!

If possible I would appreciate answer in SQL Lite but probably any syntax would work. I just need to understand the logic how to compose the tables.

Thank you

answered question

1 Answer


Just a little logic switch should do the trick


Select Col1
      ,Hits = sum(1) 
 From (
        Select Col1=case when col1<Col2 then Col1 else Col2 end
              ,Col2=case when col1<Col2 then Col2 else Col1 end
          from YourTable
      ) A
 Group By Col1,Col2


Col1    Col2    Hits
Jay     Jo      4
Linda   Luke    3
Luke    Wendy   1

posted this

Have an answer?


Please login first before posting an answer.