CODEWITHSUNDEEP
mysql
Fibericon
Fibericon

Reputation: 5793

Selecting sets with the top 10% of combined votes among objects

For some background, I previously asked about retrieving sets with highest number of combined votes among objects. That works great for getting the top 25, but now I would like to get the top 10%, ordered by rankset's timestamp. Here are the tables in question:

CREATE  TABLE IF NOT EXISTS `rankset` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `name` TEXT NOT NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB

CREATE  TABLE IF NOT EXISTS `item` (
  `id` BIGINT NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(128) NOT NULL ,
  `rankset` BIGINT NOT NULL ,
  `image` VARCHAR(45) NULL ,
  `description` VARCHAR(140) NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB

CREATE  TABLE IF NOT EXISTS `mydb`.`vote` (
  `id` BIGINT NOT NULL AUTO_INCREMENT ,
  `value` TINYINT NOT NULL ,
  `item` BIGINT NOT NULL ,
  `user` BIGINT NOT NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB

I'd list what I've tried so far, but I honestly don't even know where to begin with this one. Here's the SQL fiddle:

http://sqlfiddle.com/#!2/fe315/9

Upvotes: 0

Views: 24

Answers (1)

scragar
scragar

Reputation: 6824

From the comments:

Look at this answer: https://stackoverflow.com/a/4474389/97513, He generates a column for the ranking of his items, use a WHERE clause that uses rank <= (SELECT COUNT(1) / 10 FROM rankset) to identify the top 10%.

I put together an SQL fiddle to demonstrate: http://sqlfiddle.com/#!2/fe315/21 - Here's another with more results so you can see it scales up when you add more rows: http://sqlfiddle.com/#!2/a02ea/1

SQL Used:

SET @rn := 0;

SELECT (@rn:=@rn+1) AS rank, q.*
FROM (
   SELECT rankset.*, COALESCE(COUNT(vote.id), 0) AS votes 
   FROM rankset, vote, item 
   WHERE item.rankset = rankset.id  
          AND vote.item = item.id 
   GROUP BY rankset.id
   ORDER BY votes DESC
) q
WHERE
    @rn <= (SELECT COUNT(1)/10 FROM rankset);

Upvotes: 1

Related Questions