MySQL Selecting count

Question

I have two tables: codes_tags and popular_tags.

codes_tags

CREATE TABLE `codes_tags` (
 `code_id` int(11) unsigned NOT NULL,
 `tag_id` int(11) unsigned NOT NULL,
 KEY `sourcecode_id` (`code_id`),
 KEY `tag_id` (`tag_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

popular_tags

CREATE TABLE `popular_tags` (
 `id` int(10) unsigned DEFAULT NULL,
 `count` int(10) unsigned DEFAULT NULL,
 KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

Sorry if this question is a bit basic, but here is what I'm trying to do. I am trying to select ten tag_id's from code_tags - these tags will be the ten that there is the most copies of (for example there might be 30 records with a tag_id of 7, so 7 would be selected if that was one of the highest). I am then inserting two values into popular_tags: The tag_id, and the tag count for that tag.

How can I do this?

Answer 1

Something along these lines:

insert into popular_tags (id, `count`)
select
    tag_id,
    count(*),
from codes_tags
group by
    tag_id
order by
    count(*) desc
limit 10

Answer 2

INSERT INTO popular_tags (id, `count`)
SELECT tag_id, count(*) FROM codes_tags 
GROUP BY tag_id ORDER BY count(*) DESC LIMIT 10