Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2006
    Posts
    2

    Unanswered: Most frequent value

    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

  2. #2
    Join Date
    Apr 2006
    Location
    Denver, Co. USA
    Posts
    242
    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.

  3. #3
    Join Date
    Oct 2006
    Posts
    2
    Indeed it works, and cts contains the number of times the most frequent values occurs! Great, thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •