Returning the modal value from a selected column?

[zq29]

Is there a function in MySQL that you can use in a query to find the modal (most common) value. So if I had a column called age, and it held the values 12, 13, 13, 13, 12, 12, 11, 14, 14, 13 - The mode would be '13'.

 

I'm thinking there must be an easy way to achieve this with a MySQL query rather than running all the results through a bunch of loops in PHP...

 

As always, any help is very much appreciated.

 

EDIT: I have just wrote a small function in PHP to work out the mode of an array of numbers, but I would still be interested if someone knows a way to do this with MySQL.

[Cook]

[!--quoteo(post=322999:date=Nov 29 2005, 06:00 PM:name=SemiApocalyptic)--][div class=\'quotetop\']QUOTE(SemiApocalyptic @ Nov 29 2005, 06:00 PM) 322999[/snapback][/div][div class=\'quotemain\'][!--quotec--]

Is there a function in MySQL that you can use in a query to find the modal (most common) value. So if I had a column called age, and it held the values 12, 13, 13, 13, 12, 12, 11, 14, 14, 13 - The mode would be '13'.

 

I'm thinking there must be an easy way to achieve this with a MySQL query rather than running all the results through a bunch of loops in PHP...

 

As always, any help is very much appreciated.

 

EDIT: I have just wrote a small function in PHP to work out the mode of an array of numbers, but I would still be interested if someone knows a way to do this with MySQL.

 

Can not test this just now, but would this be a step in the right direction:

 

[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] MAX(COUNT(`field`)) FROM `table` GROUP BY `field` [!--sql2--][/div][!--sql3--]

[zq29]

[!--quoteo(post=323001:date=Nov 29 2005, 10:48 AM:name=Cook)--][div class=\'quotetop\']QUOTE(Cook @ Nov 29 2005, 10:48 AM) 323001[/snapback][/div][div class=\'quotemain\'][!--quotec--]

Can not test this just now, but would this be a step in the right direction:

 

[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] MAX(COUNT(`field`)) FROM `table` GROUP BY `field` [!--sql2--][/div][!--sql3--]

Thanks again Cook, that appears to make sense! I'll give it a go in a bit.

[Cook]

Or this:

 

[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] `field`, COUNT(`field`) AS `count` FROM `table` GROUP BY `field` ORDER BY `count` DESC LIMIT 1 [!--sql2--][/div][!--sql3--]