MySQL latest records with condition

Question

I need to get the latest records, which are repeated more than two times.

structure:

CREATE TABLE IF NOT EXISTS `tags` (
  `tag_n` int(10) NOT NULL AUTO_INCREMENT,
  `post_n` int(10) NOT NULL,
  `tag` varchar(30) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`tag_n`),
  KEY `tag` (`tag`),
  KEY `post_n` (`post_n`),
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

Records:

SELECT * FROM tags ORDER BY post_n DESC LIMIT 0 , 30

My query:

SELECT tag, COUNT(post_n) AS tags_count 
FROM tags
GROUP BY tag HAVING tags_count>=2
ORDER BY post_n DESC LIMIT 5

But I get wrong results, latest must be "xpro", can't understand what`s wrong.

Any ideas?

p.s. sorry for my english.

Answer 1

Version 1

SELECT tag, COUNT(post_n) AS tags_count ,max(post_n) as max_post_n
FROM tags
GROUP BY tag HAVING tags_count>=2
ORDER BY max_post_n DESC LIMIT 5

Version 2 Faster SELECT slower insert. Stats updates online

CREATE TABLE IF NOT EXISTS `tags` (
  `tag_n` int(10) NOT NULL AUTO_INCREMENT,
  `post_n` int(10) NOT NULL,
  `tag` varchar(30) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`tag_n`),
  KEY `tag` (`tag`),
  KEY `post_n` (`post_n`),
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE IF NOT EXISTS `tags_stats` (
  `tag` varchar(30),
  `tags_count` int(10) NOT NULL,
  `max_post_n` int(10) NOT NULL,
  PRIMARY KEY (`tag`)

) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin;


Pseudo code :

INSERT INTO tags(tag,post_n) VALUES(tag_value, post_n_value);

row = SELECT * FROM tags_stats WHERE tag=tag_value;
if not row:
    INSERT INTO tags_stats(tag,tags_count,max_post_n) VALUES(tag_value,1,post_n_value);
else:
    if row.max_post_n < post_n_value
        UPDATE tags_stats SET tags_count=tags_count+1,
          max_post_n=post_n_value WHERE tag=tag_value;
    else:
        UPDATE tags_stats SET tags_count=tags_count+1,
          WHERE tag=tag_value;
#######################################
SELECT * FROM tags_stats ORDER BY max_post_n DESC;

Answer 2

Solution:

SELECT tag, COUNT(*)AS tags_count FROM
(
  SELECT post_n, tag FROM tags
  ORDER BY post_n DESC LIMIT 20
) AS temp
GROUP BY tag HAVING tags_count>=2
ORDER BY post_n DESC LIMIT 5

Of course need to change limit in the first selection, otherwise there will be plenty to choose from.

P. S. Sorry that is poorly formulated question, my english very bad.