Most frequently occurring values in MySQL database

How can you select the most frequently occurring values in a MySQL database? Assuming I have a num field, with these rows:

1, 1, 3, 1, 1, 17, 12, 1, 3, 17, 3

If I wanted to find the three most fr开发者_如何学Cequently occurring values, 1, 3, and 17, how would I go about doing this (and getting a count)?

Would the proper method be to SELECT UNIQUE and do a tally for each individual value? Is there a more efficient method? This seems like it'd fail for larger data sets.

Thanks for the help! This is in PHP, with a MySQL database.

Something like this should work:

SELECT num, COUNT(num) AS ct
FROM yourtable
GROUP BY num
ORDER BY ct DESC

This tells MySQL to group the results by the num column. By also also selecting COUNT(num) as the number of rows found for each num value, we can then issue the ORDER BY which will order the results according to how many rows each value of num has.

With these values in the table:

1, 1, 3, 1, 1, 17, 12, 1, 3, 17, 3

The results would be:

   num  | ct
============
     1  |  5
     3  |  3
    17  |  2
    12  |  1

MySQL Cookbook has a section on generating frequency distributions, which basically comes down to something like

SELECT num, COUNT(num) AS occurrence
FROM table
GROUP BY num