Results 1 to 5 of 5
  1. #1
    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)

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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?
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    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. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    how about something like:

    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.
    Dave

  5. #5
    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
  •