I have this very simple table---- Table structure for table `list`--CREATE TABLE IF NOT EXISTS `list` ( `idx_list` bigint(20) unsigned NOT NULL auto_increment, `idx_users` bigint(20) unsigned NOT NULL, `idx_items` bigint(20) unsigned NOT NULL, `item_desc` text, PRIMARY KEY (`idx_list`), KEY `idx_user` (`idx_users`,`idx_items`), FULLTEXT KEY `item_desc` (`item_desc`)) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=48 ;---- Dumping data for table `list`--INSERT INTO `list` (`idx_list`, `idx_users`, `idx_items`, `item_desc`) VALUES(48, 9, 36, NULL),(46, 3, 2, NULL),(44, 4, 2, NULL),(43, 4, 36, NULL),(35, 5, 26, NULL),(33, 5, 31, NULL),(34, 5, 32, NULL),(36, 5, 2, NULL),(37, 5, 33, NULL),(39, 5, 34, NULL),(47, 3, 4, NULL);
so what I'm trying to do is make a "Top 10" kind of list. This table will eventually have a LOT more data in it.I have no idea if there's even a way to write a query for this.the list is supposed to be made up like this...what ever 'idx_items' value is repeated the MOST is the top, whatever the next most repeated is 2nd, and so on.i'd need data to come back something like...each row having idx_itemsCOUNT(idx_items)sorry if i haven't described this very wellthe list i want back from that data should be something like...idx_items, count(idx_items)2, count(3)36, count(2)26, count(1)31, count(1)32, count(1)33, count(1)34, count(1)obviously this will work out better than that when there's more data, but that's the ideathe end query will be more complicated, but if someone can point me in the right direction i can take it from there!ty in advance!life is better without too much technology
top 10 type of list
Posted - 2008-03-05 : 18:41:47
