Geek Answers Handbook

GROUP Combinations of columns, not permutations

On the website I worked on, users are allowed to send data to the table, and when they send the data, they are offered the name of the partner, as two people are working on receiving the data. I am trying to create a table with a high rating, which lists the best partnerships, AKA - a combination (not a rearrangement) of the partner and the submitter, which most often appears on the table.

The GROUP team works just fine for this, but I hit a bit on the combination / permutation issue. Currently, when I group them, it only checks permutations of senders and partners, not combinations. The problem is that often the partner chooses to alternate between the person who is the submitter and the other as the partner, so I actually have two possible permutations of the groups that I can pull out.

Currently, I have a code that will pull out one table of high scores for the Submitter and Partner shipments, as well as another table with a high rating of permutations of the Partner and Sender. I need to combine these results (possibly ungrouped and not ordered) when partner = submitter or submitter = partner, and then group them and order then in descending order.

SELECT submitter, partner, COUNT(*) FROM submissions GROUP BY submitter, partner;

The above code will return a table with calculations of the specific permutation of the submitter and partner, but if the same two people are partners and senders, just by switching, they are not considered the same group.

Does anyone know the code for this?

+5
source share
1 answer

Not the nicest solution, but it gives you the answer you want:

SELECT Person1, Person2, COUNT(*) FROM (
    SELECT 
        CASE WHEN submitter < partner THEN submitter ELSE partner END AS Person1,
        CASE WHEN submitter >= partner THEN submitter ELSE partner END AS Person2
    FROM submissions
) Q
GROUP BY Person1, Person2
+6
source

More articles:


All Articles