MySQL: Order by Count(*)?

Question

I have a table with the following structure (some of you may recognize it as part of a standard vBulletin installation):

mysql> SHOW CREATE TABLE adminlog\G
*************************** 1. row ***************************
       Table: adminlog
Create Table: CREATE TABLE `adminlog` (
  `adminlogid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `userid` int(10) unsigned NOT NULL DEFAULT '0',
  `dateline` int(10) unsigned NOT NULL DEFAULT '0',
  `script` varchar(50) NOT NULL DEFAULT '',
  `action` varchar(20) NOT NULL DEFAULT '',
  `extrainfo` varchar(200) NOT NULL DEFAULT '',
  `ipaddress` varchar(15) NOT NULL DEFAULT '',
  PRIMARY KEY (`adminlogid`),
  KEY `script_action` (`script`,`action`)
) ENGINE=MyISAM AUTO_INCREMENT=698189 DEFAULT CHARSET=latin1
1 row in set (0.02 sec)

mysql>

The following query in PHP would select the distinct scripts used by a certain user:

"SELECT DISTINCT script FROM adminlog WHERE userid = " . intval($userid)

How can I get the query to order the scripts by number of occurrences (for that specific userid), descending? ORDER BY COUNT(*) doesn't do that.

Answer 1

I think you could solve this with group and count.

SELECT count(script) ocurrences, script FROM adminlog GROUP BY script ORDER BY ocurrences DESC;

here you have a link for testing

http://sqlfiddle.com/#!2/b6f78/8

Answer 2

ORDER BY COUNT(*) should work, but get rid of the DISTINCT.

Try:

"SELECT script FROM adminlog WHERE userid = " . intval($userid)
. "GROUP BY script ORDER BY COUNT(*)"