Page 1 of 1

[SOLVED] selecting results based on how often they appear

Posted: Tue Jun 21, 2005 4:47 pm
by phase
hey, this may seem like an obvious question, but i am struggling thinking of a way to do it.

what i am trying to do is to use a select query to find out who has been the most active user on my site.

i have a mysql table which has a signup page, this table has the user id stored inside it.

so i want to select which user appears in that table the most.

Code: Select all

$results = $db->sql_query("select player_id from ".$prefix1."_csignups GROUP BY player_id LIMIT 1");
this is what i have at the moment, it groups all the player ids and can output them if i desire, but all i want to do is limit is to one result and display that one player id. i feel i need an ORDER clause, but i have tried order by player id but that wont work as it will just list the first grouped id numerically, i want to display the player who appears the most.

if anyone can help id be very grateful

bye for now.

phase

Posted: Tue Jun 21, 2005 6:16 pm
by Burrito
was chatting with D11 and he came up with this...it works a peach:

Code: Select all

SELECT COUNT(`field`) as `t`, `field` FROM `table` GROUP BY `field` ORDER BY `t` DESC

Posted: Tue Jun 21, 2005 7:05 pm
by phase
Thank you very much, works perfectly!