mySQL: select the top-100 records that are repetited by n-size.


  • QA Engineer

    How do you choose TOP-100 records where entry_id is repeated more than three times?

    CREATE TABLE `bookmarks` (
        `id` INT(11) NOT NULL AUTO_INCREMENT,
        `user_id` INT(11) NOT NULL,
        `entry_id` INT(11) NOT NULL,
        `date_added` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (`id`),
        UNIQUE INDEX `user_id_entry_id` (`user_id`, `entry_id`)
    )
    


  • That's how it works.

    SELECT *, COUNT(id) FROM bookmarks
    GROUP BY entry_id HAVING COUNT(id)>3
    ORDER BY COUNT(id) DESC
    LIMIT 100;
    

    Grouped under entry_id choosing only those records COUNT(id) more than n (in our example = 3).
    Next, we'll sort out the references.COUNT(id)(d) otherwise
    and we only take 100 records.


Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2