Jump to content

webdev1

Members
  • Posts

    15
  • Joined

  • Last visited

Everything posted by webdev1

  1. THANK YOU so much. I learnt something new. I normalized the table and the SQL statement works. Thank you Barand & vinny42
  2. 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'
  3. Thank you for your help. Yes any combination of 3 columns that counts occurrences in desc order to find the most common triple sequence.
  4. Using the SQL statement discussed earllier: SELECT balls, COUNT(*) as occurence FROM ( SELECT CONCAT_WS(',', Ball1, Ball2, Ball3) as balls, Ball1 as `A`, Ball2 as `B`, Ball3 as `C` FROM random UNION ALL SELECT CONCAT_WS(',', Ball2, Ball3, Ball4) as balls, Ball2 as `A`, Ball3 as `B`, Ball4 as `C` FROM random UNION ALL SELECT CONCAT_WS(',', Ball3, Ball4, Ball5) as balls, Ball3 as `A`, Ball4 as `B`, Ball5 as `C` FROM random UNION ALL SELECT CONCAT_WS(',', Ball4, Ball5, Ball6) as balls, Ball4 as `A`, Ball5 as `B`, Ball6 as `C` FROM random UNION ALL SELECT CONCAT_WS(',', Ball5, Ball6, Ball1) as balls, Ball5 as `A`, Ball6 as `B`, Ball1 as `C` FROM random ) AS t GROUP BY balls ORDER BY occurence desc LIMIT 10 The highest I get is 3 but if you do a direct search for numbers 9,27,42 you will get 9 occurrences This proves the logic in the above SQL statement is at fault.
  5. Thank you for your replies. The data sent earlier was from the wrong table. Here is the correct data (55 rows) comma delimited rows terminated with colon : 1, 3, 5, 14, 22, 30, 44: 2, 6, 12, 15, 16, 31, 44: 3, 11, 17, 21, 29, 30, 40: 4, 26, 35, 38, 43, 47, 49: 5, 3, 5, 9, 13, 14, 38: 6, 2, 3, 27, 29, 39, 44: 7, 9, 17, 32, 36, 42, 44: 8, 2, 5, 21, 22, 25, 32: 9, 7, 17, 23, 32, 38, 42: 10, 6, 16, 20, 30, 31, 47: 11, 4, 16, 25, 26, 31, 43: 12, 1, 7, 37, 38, 42, 46: 13, 15, 18, 29, 35, 38, 48: 14, 16, 19, 21, 29, 36, 45: 15, 5, 8, 10, 18, 31, 33: 16, 11, 12, 17, 26, 36, 42: 17, 2, 13, 22, 27, 29, 46: 18, 9, 18, 19, 24, 31, 41: 19, 4, 17, 41, 42, 44, 49: 20, 22, 25, 30, 32, 41, 43: 21, 14, 17, 22, 24, 42, 47: 22, 1, 4, 6, 23, 26, 49: 23, 8, 18, 20, 33, 36, 38: 24, 9, 15, 22, 31, 34, 48: 25, 5, 14, 17, 35, 43, 48: 26, 7, 16, 25, 26, 28, 41: 27, 15, 16, 17, 28, 32, 46: 28, 12, 13, 25, 37, 44, 45: 29, 1, 21, 29, 31, 32, 40: 30, 12, 15, 26, 44, 46, 49: 31, 2, 3, 17, 23, 40, 43: 32, 2, 3, 4, 19, 23, 40: 33, 2, 3, 17, 22, 40, 42: 34, 10, 18, 34, 35, 39, 48: 35, 8, 10, 26, 34, 48, 49: 36, 10, 12, 22, 34, 46, 48: 37, 14, 18, 19, 35, 44, 45: 38, 15, 19, 25, 36, 44, 45: 39, 11, 17, 19, 40, 44, 45: 40, 19, 20, 23, 42, 44, 45: 41, 24, 25, 26, 38, 39, 40: 42, 2, 3, 21, 22, 23, 40: 43, 4, 14, 15, 17, 21, 31: 44, 14, 15, 17, 26, 35, 46: 45, 14, 15, 17, 29, 30, 33: 46, 9, 10, 16, 18, 27, 42: 47, 1, 5, 9, 27, 42, 45: 48, 9, 16, 23, 26, 27, 42: 49, 5, 9, 27, 35, 42, 43: 50, 9, 27, 28, 31, 41, 42: 51, 9, 10, 11, 27, 42, 47: 52, 9, 21, 27, 36, 42, 48: 53, 9, 25, 27, 31, 42, 45: 54, 9, 27, 29, 37, 42, 44: 55, 2, 17, 23, 38, 40, 49: The first number is the id. The other 6 columns is the random data columns. This data is a test data. There are lines with common triple results maximum should be 9.
  6. The table random contains id (int 5) & ball1, ball2, ball3, ball4, ball5, ball6 all of int(2) types. There are no varchar types. I am using phpmyadmin (latest version) to do the SQL searches. Here is some test data (30 rows): 3 5 14 22 30 44 10 6 12 15 16 31 44 37 11 17 21 29 30 40 31 26 35 38 43 47 49 28 3 5 9 13 14 38 30 2 3 27 29 39 44 6 9 17 32 36 42 44 16 2 5 21 22 25 32 46 7 17 23 32 38 42 48 6 16 20 30 31 47 4 4 16 25 26 31 43 21 1 7 37 38 42 46 20 15 18 29 35 38 48 5 16 19 21 29 36 45 43 5 8 10 18 31 33 28 11 12 17 26 36 42 13 2 13 22 27 29 46 36 9 18 19 24 31 41 21 4 17 41 42 44 49 24 22 25 30 32 41 43 29 14 17 22 24 42 47 34 1 4 6 23 26 49 8 8 18 20 33 36 38 46 9 15 22 31 34 48 23 5 14 17 35 43 48 22 7 16 25 26 28 41 19 15 16 17 28 32 46 22 12 13 25 37 44 45 9 1 21 29 31 32 40 27 12 15 26 44 46 49 14 Without conducting test I think this should have more than 1 common occurrence of 3
  7. Thank you for your help. Your expertise is appreciated. The results are good but not accurate, there appears to be some combinations missing which I found by random search. A group of 3 numbers occurred 10 times and the highest result I am getting with this SQL query is 7. Is there anything missing?
  8. Thank you I appreciate your help. With this SQL query I get the following result: balls occurence 3,5,14 5069 I wanted a list of more than one result and the occurrence appears to be incorrect as there are only 1845 rows in the table. The table structure is as follows and Ball data is numeric 1-50: id Ball1 Ball2 Ball3 Ball4 Ball5 Ball6 Can the SQL statement be changed to give a list like this: balls occurence 3,5,14 99 23,34,44 55 etc etc to find the 3 common combination occurrences and how many occurence times in sequential order by highest first
  9. Thank you however I get the error: #1248 - Every derived table must have its own alias when running: SELECT balls, COUNT(*) as occurence FROM ( SELECT CONCAT_WS(',', Ball1, Ball2, Ball3) as balls, Ball1 as `A`, Ball2 as `B`, Ball3 as `C` FROM random UNION SELECT CONCAT_WS(',', Ball2, Ball3, Ball4) as balls, Ball2 as `A`, Ball3 as `B`, Ball4 as `C` FROM random UNION SELECT CONCAT_WS(',', Ball3, Ball4, Ball5) as balls, Ball3 as `A`, Ball4 as `B`, Ball5 as `C` FROM random UNION SELECT CONCAT_WS(',', Ball4, Ball5, Ball6) as balls, Ball4 as `A`, Ball5 as `B`, Ball6 as `C` FROM random )
  10. This SQL query appears to be dependent upon input by selecting $ball1-6 in the WHERE section. It will not work if I use this in mysql and $ball* variable needs a number. I want an SQL query that does the search without any input. i.e. it finds common ball number matches with more than one instance. Is this possible?
  11. Thanks I will try this query. FYI the data is in numerical order for each draw. Table columns are id, ball1, ball2, ball3, ball4, ball5, ball6.
  12. Hi thanks for replying. I have created a table populated with id & 6 columns for the random ball numbers. The query above does a search for 3 chosen numbers to see if they exist in the database. I want to analyse the data to find common sets of 3 number sequences and count how many times they exist without selecting the ball numbers to count. So a sequence of say 2 4 6 may exist more than once but I would like to know how many times. I can then use the count number for further analysis. Is this possible in SQL?
  13. Hi, I am using php 5.4.7 & MySQL 5.5.21 I am working on a academic research project for analysing random sequences. I am using data from a lottery as source data as these are random sequences of numbers up to 50. I am using SQL query to do a table search on 3 random numbers (php): SELECT id, , GROUP_CONCAT(ball ORDER BY ball) balls FROM ( SELECT id Ball1 ball FROM random UNION SELECT id,Ball2 ball FROM random UNION SELECT id, Ball3 ball FROM random UNION SELECT id, Ball4 ball FROM random UNION SELECT id, Ball5 ball FROM random UNION SELECT id, Ball6 ball FROM random ) x WHERE ball IN ( $ball1, $ball2, $ball3, $ball4, $ball5, $ball6) GROUP BY id HAVING COUNT(*) = 3 which works for 3 chosen numbers however how do I create an SQL statement to find 3 common sequences (without selecting the numbers) that occur more than once? I assume it maybe possible with mysql regex statement but do not know how to create this statement. I hope someone can help a newbie. Thanks in advance.
×
×
  • 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.