Select by number of [field]

it's about strawberries and banana :-)

[ticatoc]

Hello,

I have a table like this :

word ¦ date
apple ¦ 2007-09-25 ...
apple ¦ 2007-09-25 ...
strawberries ¦ 2007-05-19 ...
banana ¦ 2007-09-15 ...
banana ¦ 2007-09-15 ...
strawberries ¦ 2006-08-11 ...
apple ¦ 2007-09-25 ...
apple ¦ 2007-09-25 ...
coca ¦ 2007-06-14 ...

There are a lot of rows in this table (thousands). "word" and "date" fields are together "unique" (there is not 2 rows with the same date).

I want to select the 10 most popular words in my table. How could I do this with efficacity please :-?

[Discovery]

I think this will work,

select word, count(*) mostpopular from table group by word order by word LIMIT 0 , 10

Thanks

[ticatoc]

Thanks for the tip, but it doesn't work completly :

$sql = "select word, count(*) mostpopular from table group by word order by word LIMIT 0 , 10";
$req = mysql_query($sql) or die(mysql_error());
echo "<ul>";
while ($db_data = mysql_fetch_assoc($req)) {
echo "<li>".$db_data['word']." - ".$db_data['date']." - ".$db_data['mostpopular']."</li>";
}

$db_data['mostpopular'] show the number of rows for each word that is in the "while".
But the words are ordered by name, not by number of occurences in the table.

For example, here I could have :

strawberries 1
apple 7
pears 1
red apple 1
coca 10

(I changed the example because my first post's example wasn't "right" do show you this error).

I tried "ORDER BY mostpopular" but it does not work :-/.

How could I ordered them by number of word's occurences :-?

[ticatoc]

Wow ok, I get it :

$sql = "select word, count(*) mostpopular from table group by word order by mostpopular DESC LIMIT 0 , 50";

Thanks :-)!

[Discovery]

ooh, it my mistake, any how its worked now :)

good, great job.

[ticatoc]

Thanks again :).

So, I have another little question about this select here : [webmasterworld.com...] If you "like" "selected problems" ^_^ ;-)...