Jump to content

SQL help for extracting common details


webdev1
Go to solution Solved by Barand,

Recommended Posts

  • Solution

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
Link to comment
Share on other sites

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'

Edited by webdev1
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.