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

Other Tech Topics
1

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


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.

2

I am assuming, the numbers are in ascending order in the columns

Also, i cheat a bit. my column name is nr_01, nr_02 so, the query only procedures nice triangle join and not all permutatiion

;

WITH cte
AS (
	SELECT *
	FROM mytable
	unpivot(nr FOR col IN (
				nr_01,
				nr_02,
				nr_03,
				nr_04
				)) up
	)
SELECT TOP 20 n1 = c1.nr,
	n2 = c2.nr,
	count(*)
FROM cte c1
CROSS JOIN cte c2
WHERE c1.id = c2.id
	AND c1.col < c2.col
GROUP BY c1.nr,
	c2.nr
ORDER BY count(*) DESC
1 Like
3

Thanks for your answer.

Ihad edit my first post and try to explain better what i want.

http://sqlfiddle.com/#!6/ea38d8/1

Result should be :

1,3 - 3 times
1,80 - 2 times
etc etc

I dont know if is ok to write this here, but i can pay to a person for helping me.

Thanks.

4

so does the query i posted, do what you want ?

5

oh . . just notice you are not using MS SQL Server. My query works on MSSQL but i am not sure about MySQL

By the way, this site is on MS SQL Server. Try posting your question on a mysql site