Is there a statistical function in MySQL to find the most popular value in a column?

Hi is there a statistical method to find the most popular occuring value within a column in a table.

For this table:

Table "resource" -linking table for M:M

+------------+---------------------+-----------+
| resource_pk| user_fk             | supply_fk |
+------------+---------------------+-----------+
| 1          | 124124              |     1     |
| 2          | 265235              |     2     |
| 3          | 253255              |     1     |
| 4          | 151255              |     1     |
| 5          | 154233              |开发者_高级运维     3     |
+------------+---------------------+-----------+

So in this example the most popular supply_fk was "1".

I want to find the most popular value of the column "supply_fk" within that table.

I tried this:

Select *
From resource
Group By *
Order By supply_fk Desc
LEFT JOIN
supply
ON
supply.supply_pk = resource.supply_fk

However all this does is give me the "supply_fk" with the highest number value which was "3" which then points to another table giving me "medical scissors".

When "1" should be "scalpel"

Im guessing there is a MySQL statistical function for this? I tried googling but all I got was Min, Max and Middle

Thanks

You can group, order by the count and get the first record:

select r.supply_fk, s.name, count(*)
from resource r
inner join supply s on s.supply_pk = r.supply_fk
group by r.supply_fk, s.name
order by count(*) desc
limit 1

You can do COUNT in mysql. See here. Try doing something like select where count is highest.

Try

SELECT TOP (1) supply_fk , COUNT(supply_fk ) AS supply_fk_count FROM supply GROUP BY supply_fk ORDER BY supply_fk_count desc

this query gives you "the most popular value of the column supply_fk within the resources table"

SELECT supply_fk, COUNT(*) as c FROM resources
   GROUP BY supply_fk ORDER BY c DESC LIMIT 1

Try this:

SELECT supply_fk, MAX(c) AS m
FROM (SELECT supply_fk, COUNT(*) AS c
     FROM resource
     GROUP BY supply_fk)
GROUP BY supply_fk

Edit: forgot you can't use reserved names for fields. Fixed now.

SELECT fk as key, count(fk) AS qty FROM mytable GROUP BY key ORDER BY qty DESC LIMIT 1;

More on this here: http://forums.devarticles.com/mysql-development-50/mysql-mean-median-mode-statistics-4385.html