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

2143 views sql
9

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

5

Just a little logic switch should do the trick

Example

Select Col1
      ,Col2
      ,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

Returns

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

posted this

Have an answer?

JD

Please login first before posting an answer.