Barand Posted September 4, 2013 Share Posted September 4, 2013 (edited) 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. Edited September 4, 2013 by Barand Quote Link to comment https://forums.phpfreaks.com/topic/281787-sql-help-for-extracting-common-details/page/2/#findComment-1448187 Share on other sites More sharing options...
vinny42 Posted September 5, 2013 Share Posted September 5, 2013 So we're not looking for sequences after all, any combination of three columns will do. Quote Link to comment https://forums.phpfreaks.com/topic/281787-sql-help-for-extracting-common-details/page/2/#findComment-1448244 Share on other sites More sharing options...
webdev1 Posted September 5, 2013 Author Share Posted September 5, 2013 Thank you for your help. Yes any combination of 3 columns that counts occurrences in desc order to find the most common triple sequence. Quote Link to comment https://forums.phpfreaks.com/topic/281787-sql-help-for-extracting-common-details/page/2/#findComment-1448247 Share on other sites More sharing options...
Solution Barand Posted September 5, 2013 Solution Share Posted September 5, 2013 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 Quote Link to comment https://forums.phpfreaks.com/topic/281787-sql-help-for-extracting-common-details/page/2/#findComment-1448248 Share on other sites More sharing options...
vinny42 Posted September 5, 2013 Share Posted September 5, 2013 Nice! Quote Link to comment https://forums.phpfreaks.com/topic/281787-sql-help-for-extracting-common-details/page/2/#findComment-1448249 Share on other sites More sharing options...
webdev1 Posted September 5, 2013 Author Share Posted September 5, 2013 (edited) 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 September 5, 2013 by webdev1 Quote Link to comment https://forums.phpfreaks.com/topic/281787-sql-help-for-extracting-common-details/page/2/#findComment-1448254 Share on other sites More sharing options...
vinny42 Posted September 5, 2013 Share Posted September 5, 2013 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. Quote Link to comment https://forums.phpfreaks.com/topic/281787-sql-help-for-extracting-common-details/page/2/#findComment-1448258 Share on other sites More sharing options...
webdev1 Posted September 5, 2013 Author Share Posted September 5, 2013 THANK YOU so much. I learnt something new. I normalized the table and the SQL statement works. Thank you Barand & vinny42 Quote Link to comment https://forums.phpfreaks.com/topic/281787-sql-help-for-extracting-common-details/page/2/#findComment-1448264 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.