Listing most common values

[fisherbln]

I am trying to check a mysql database for the most common values within a certain row. I want to output the $variablenumber (say 5) most frequent values.
How would I go about this in php?
Thanks

 

[AnakinPt]

if you have something like this:

votes
1
2
1
2
1
3
2
1
2

you want something like this
votes total
1 4
2 4
3 1

The query is like this:
SELECT votes,count(votes) as total from table group by votes order by total desc,votes asc

Note: in the order by, some databases use the alias while others use the expression in select, so if doen't works, try count(votes) instead of total in the ORDER BY clause. I don't know how mysql works cause i didn't work in it in the last 15 months. Anikin
Hugo Alexandre Dias
Web-Programmer
anikin_jedi@hotmail.com

 

[skicamel]

Not sure if the syntax is the same for MySQL. This is for MSSQL. Same as Anakin submitted, but throw a top N in there....

Select top 5 votes, count(votes)...etc...

This will display only the top 5 results.

 

[fisherbln]

At the risk of sounding like a complete idiot, I am now having trouble getting this result displayed. Anyone can point me in the right direction?

Thank you

 

[AnakinPt]

What code are you using to display the info? (just the needed code... forget open databases and other junk unneeded here).

Mainly you have to do:
$query="SELECT votes,count(votes) from ...";
$result=mysql_query($query);
while($array=mysql_fetch_array($result)){
echo "$array[votes]-$array[total]";
}

or something like this.
Anikin
Hugo Alexandre Dias
Web-Programmer
anikin_jedi@hotmail.com