# Thread: Get 20 most frequent pairs(2/2) and triplets(3/3) of numbers mysql

1. Registered User
Join Date
Nov 2015
Posts
3

## Unanswered: Get 20 most frequent pairs(2/2) and triplets(3/3) of numbers mysql

I'm trying to get the 20 most frequent pairs(2/2) and triplets(3/3) of numbers, my table looks like this:

Code:
```+----+------+------+------+------+------+------+------+------+------+------+
| id | nr_1 | nr_2 | nr_3 | nr_4 | nr_5 | nr_6 | nr_7 | nr_8 | nr_9 | nr_10|
+----+------+------+------+------+------+------+------+------+------+------+
| 1  | 1    | 3    | 19   | 23   | 28   | 32   | 39   | 42   | 60   | 80   |
+----+------+------+------+------+------+------+------+------+------+------+
| 2  | 1    | 3    | 18   | 24   | 29   | 33   | 40   | 43   | 61   | 80   |
+----+------+------+------+------+------+------+------+------+------+------+
| 3  | 1    | 2    | 3    | 25   | 30   | 34   | 41   | 44   | 62   | 78   |
+----+------+------+------+------+------+------+------+------+------+------+```
I need query to find the 20 most frecvent pairs of numbers from all my rows.

Exemple :

1,3 (3 times)

1,80 (2 times)

3,80 (2 times)

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
sorry don't understand your concept of pairings

I can see that you have 1 appearing 3 times in nr_1, so that could indeed be 3 pairs (ID1+ID2, ID1+ID3 & ID2+ID3)

...but 1,80?, 80 only appears twice in column 10, 3 appears 3 times twice in Nr_2 & once in nr_3
So I could see 2,3 & 10,80

..so I cannot see any logic behind 1,80 & 3,80 as pairings
..perhaps if you could explain why / how you are expecting the results you are?

3. Registered User
Join Date
Nov 2015
Posts
3
Lets put it in another way.

Forget about ID 1,2 and 3.

Imagine that there is a lottery with 10 drawn numbers out of 80 and in the table are the results.

I want a way to find the most drawn pairs and triplets from any numbers of draws.

Per example:

Pairs of numbers(two numbers that have been drawn toghether):

-the numbers 1 and 3 have been drawn together 3 times, which makes it the most drawn pair of numbers out of the 3 draws.

-the numbers 1 and 80 have been drawn together 2 times along with 3,80 which also has been drawn 2 times out of 3 draws.

As for most drawn triplets( any combination of 3 numbers):

-1,3,80 is the most common triplet, being drawn together 2 times out of the 3 draws.

Thanks for your answer and i hope i explained better this time.

4. Registered User
Join Date
Dec 2007
Location
Richmond, VA
Posts
1,328

Code:
```select id
,sum(case
when nr_1 =nr_2 then 1
when nr_1 =nr_3 then 1
when nr_1 =nr_4 then 1
when nr_1 =nr_5 then 1
when nr_1 =nr_6 then 1
when nr_1 =nr_7 then 1
when nr_1 =nr_8 then 1
when nr_1 =nr_9 then 1
when nr_1 =nr_10 then 1
end
from my_nr_table
group by id
having count(*) >=1
fetch first 20 rows only```
will tell you when it occurs more than once.

5. Registered User
Join Date
Nov 2015
Posts
3
I need to find out the pairs of numbers with most appearances, not which pairs have been drawn at least once.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•