I have a table with the following structure (some of you may recognize it as part of a standard vBulletin 3 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.
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