CODEWITHSUNDEEP
mysql
Mark
Mark

Reputation: 323

Group and rank top N rows by category

I have a table with the columns category and votes. I've tried multiple solutions before with very little success; usually what would happen is that instead of returning the top 3 items in each category, it returns all of the items available.

SELECT `id`, `user_id`, `full_name`, `category`, `year`, `month`, `thumbnail_photo`, `photo_title`, `votes`
FROM
    (
    SELECT `id`, `user_id`, `full_name`, `category`, `year`, `month`, `thumbnail_photo`, `photo_title`, `votes`,
        @category_rank := IF(@current_category = category, @category_rank + 1, 1) AS category_rank,
        @current_category := category
    FROM `photo_contest`
    ORDER BY
        `category`,
        `votes` DESC
) ranked
WHERE
    category_rank <= 3
    AND `year` = '2017'
    AND `month` = 'April'
    AND `votes` > 0

This particular solution was adapted from SQLines. What I ultimately want to do is to turn a table like this:

Name      | Category | Votes
--------- | -------- | -----
Name Foo  | CatFoo   | 0
Name Bar  | CatFoo   | 1
Name Baz  | CatFoo   | 10
Name Quux | CatFoo   | 200
Name ooF  | CatBar   | 50
Name raB  | CatBar   | 300
Name zaB  | CatBar   | 10
Name xuuQ | CatBar   | 200

...to:

Name      | Category | Votes
--------- | -------- | -----
Name Quux | CatFoo   | 200
Name Baz  | CatFoo   | 10
Name Bar  | CatFoo   | 1
Name raB  | CatBar   | 300
Name xuuQ | CatBar   | 200
Name ooF  | CatBar   | 50

...with the other WHERE statements included. Year, month, and minimum votes.

Upvotes: 0

Views: 98

Answers (3)

Test Mirror
Test Mirror

Reputation: 344

It sounds like your PHPMyAdmin needs an upgrade or a replacement. Meanwhile you might want to try @Stefano Zanini's non-MySQL specific SQL:

SELECT
    t1.`id`, t1.`category`, t1.`year`, t1.`month`,
    t1.`votes`, count(distinct t2.`id`) as rank
FROM photo_contest t1
INNER JOIN photo_contest t2 ON
    t1.`category` = t2.`category` AND
    t1.`votes` <= t2.`votes`
WHERE t1.`votes` > 0
GROUP BY t1.`id`, t1.`category`, t1.`votes`
HAVING count(distinct t2.`id`) <= 3
ORDER BY t1.`category`, rank;

It's available on sqlfiddle. If you think this solution suits you better please credit @Stefano Zanini's answer instead of this one.

Upvotes: 0

Test Mirror
Test Mirror

Reputation: 344

Your subquery tries to calculate ranking over the entire table. If you only want to rank for the selected year-month with votes > 0, you should copy those conditions into the subquery as its own WHERE conditions.

UPDATE:

Looks like it's the missing ORDER BY in the outer-query that causes the said problem. I've created the following DDL/SQL at sqlfiddle.

CREATE TABLE IF NOT EXISTS `votes` (
  `id` INT NOT NULL,
  `category` VARCHAR(10) NULL,
  `year` VARCHAR(4) NULL,
  `month` VARCHAR(2) NULL,
  `votes` INT
)
ENGINE = InnoDB;

INSERT INTO `votes` VALUES
(10, 'cat1', '2016', '05', 300),
(10, 'cat1', '2016', '06', 200),
(10, 'cat2', '2016', '05', 500),
(11, 'cat1', '2016', '05', 200),
(11, 'cat2', '2016', '05', 0),
(11, 'cat2', '2016', '06', 100),
(12, 'cat1', '2016', '05', 400),
(12, 'cat2', '2016', '05', 150),
(13, 'cat1', '2016', '05', 350),
(13, 'cat2', '2016', '05', 100),
(13, 'cat2', '2016', '06', 150),
(14, 'cat1', '2016', '05', 0),
(14, 'cat2', '2016', '05', 450);

SELECT `id`, `category`, `year`, `month`, `votes`
FROM (
    SELECT `id`, `category`, `year`, `month`, `votes`,
        @category_rank := IF(@current_category = category, @category_rank + 1, 1) AS category_rank,
        @current_category := category
    FROM `votes`
    WHERE
        `year` = '2016'
        AND `month` = '05'
        AND `votes` > 0
    ORDER BY
        `category`,
        `votes` DESC
) ranked
WHERE
    category_rank <= 3
ORDER BY
    `category`,
    `votes` DESC;

Upvotes: 1

Stefano Zanini
Stefano Zanini

Reputation: 5916

I'm not an expert with MySQL, so I propose you a different (standard SQL) approach: you can join the table with itself on Category and on Votes being less or equal to the votes of the current row.

select  t1.Name, t1.Category, t1.Votes, count(distinct t2.Name) as rank
from    photo_contest t1
join    photo_contest t2
on      t1.Category = t2.Category and
        t1.Votes <= t2.Votes
/*where whatever you want*/
group by t1.Name, t1.Category, t1.Votes
having  count(distinct t2.Name) <= 3
order by t1.Category, rank

I tested it here and it seems to do what you asked for

Upvotes: 0

Related Questions