I have a table with a int field of which I want the most frequent value. If rows contain the values, 8, 4, 8, 2, 9, the query I'm searching for would return 8. My current solution is to go through all records, and count the frequency of the values using PHP. I hope there is a better solution, something like:
select * from table1 order by frequency(row1) desc
This will probably work -
select your_column_name, count(*) as cnt from table1 group by your_column_name order by cnt desc limit 1
This will return one row with the value followed by the count of that value. If there are ties, it will return the one which it encountered first (lowest row number). To let your PHP program deal with ties, increase the limit x number or eliminate the limit x clause if you want all the results.