SQL help for extracting common details

[Barand]

Ah, at last!

 

Webdev, you have now given us a clue about what you are looking for.

 

That last post (+ sample data) is one you should have made a page back.

[vinny42]

So we're not looking for sequences after all, any combination of three columns will do.

[webdev1]

Thank you for your help. Yes any combination of 3 columns that counts occurrences in desc order to find the most common triple sequence.

[Barand]

I normalized the data into table randomn.

 

id int,

seq int

ball int

 

then

    SELECT CONCAT_WS(',', A,B,C) as balls, COUNT(*) as occ
    FROM
    (
    SELECT a.id, a.ball as A, b.ball as B, c.ball as C
    FROM randomn a
    JOIN randomn b ON a.id = b.id AND a.seq < b.seq
    JOIN randomn c ON c.id = b.id AND b.seq < c.seq
    ) as X
    GROUP BY balls
    ORDER BY occ DESC

[vinny42]

Nice!

[webdev1]

Thank for your quick response Barand

The SQL statement does not run on the table structure I have: 

id int

ball1 - ball6 int(2)

 

total 7 columns

 

& if I do run this I get the error #1054 - Unknown column 'a.ball' in 'field list'

[vinny42]

Barand normalized the data before applying this query, as I suggested earlier.

Basically he transformed the data from 

 

id ball1, ball2, ball3 etc

1, 5,7,9,...

 

to:

 

id, ballvalue, ballposition

1,5,1

1,7,2

1,9,2

 

which you can do using something like 

 

CREATE TABLE random_normalized AS 

SELECT * FROM

(

SELECT id, ball1 AS ball, 1 AS seq FROM random

UNION ALL

SELECT id, ball2 AS ball, 2 AS seq FROM random

UNION ALL

...

) AS n;

 

That table can then be used by Barand's query.

[webdev1]

THANK YOU so much.

I learnt something new.

I normalized the table and the SQL statement works.

Thank you Barand & vinny42