Fetching top 3 from another table for every row in a 3rd table

Question

I have 3 tables, one which stores pictures and one that stores votes for pictures (pictures and picture_votes). The last table is categories, which stores the different categories a picture can belong to.

Here are the tables (non-relevant columns omitted);

- Table `pictures`
picture_id  INT
category_id INT

and

- Table `picture_votes`
vote        TINYINT
picture_id  INT

and finally

- Table `categories`
category_id INT

What I want to do is to select the top 3 most voted pictures for each category.

I'm really lost and don't know how to do this most effectively..

score 2 · Answer 1 · edited Dec 15 '13 at 19:38

If you can accept them in one row per category as a comma delimited list:

select pv.category_id,
       substring_index(group_concat(pv.picture_id order by numvotes desc), ',', 3) as Top3
from (select p.category_id, p.picture_id, count(*) as numvotes
      from picture_votes pv join            
           pictures p             
           on p.picture_id = pv.picture_id
      group by p.category_id, p.picture_id
     ) pv
group by pv.category_id;

score 0 · Answer 2 · answered Dec 15 '13 at 17:31

I came up with this;

(SELECT p.*
 FROM
  pictures p
 LEFT JOIN
  picture_votes pv
   ON pv.picture_id = p.picture_id
 WHERE p.category_id = n
 GROUP BY p.picture_id
 ORDER BY SUM(pv.vote) DESC
 LIMIT 3)
 UNION
 (SELECT ...)
 UNION
 (SELECT ...)
 --And so on for every category_id (there are 9)

This seems like a veeeery bad solution and the query takes way too long.

Tin Tran · Answer 3 · 2013-12-18T19:11:20.080

sqlFiddle

SELECT category_id,picture_id,ranking FROM
(
 select c.category_id,(select p.picture_id
                  from pictures p, picture_votes pv
                  where p.picture_id = pv.picture_id
                  and p.category_id = c.category_id
                  group by p.picture_id
                  order by sum(pv.vote) desc
                  limit 0,1)as picture_id,1 as ranking
   from categories c

 union

 select c.category_id,(select p.picture_id
                  from pictures p, picture_votes pv
                  where p.picture_id = pv.picture_id
                  and p.category_id = c.category_id
                  group by p.picture_id
                  order by sum(pv.vote) desc
                  limit 1,1)as picture_id,2 as ranking 
   from categories c

 union
 select c.category_id,(select p.picture_id
                  from pictures p, picture_votes pv
                  where p.picture_id = pv.picture_id
                  and p.category_id = c.category_id
                  group by p.picture_id
                  order by sum(pv.vote) desc
                  limit 2,1)as picture_id,3 as ranking
   from categories c
)result
WHERE picture_id is not null
order by category_id asc,ranking asc

or this sqlFiddle

SELECT picture_id,category_id,sumvotes,voteOrder
FROM
    (SELECT picture_id,category_id,sumvotes,
       IF(@prevCat <> category_id,@voteOrder:=1,@voteOrder:=@voteOrder+1)
       as voteOrder,
       @prevCat:=category_id
     FROM(SELECT p.picture_id,
                 p.category_id,
                 SUM(pv.vote) as sumvotes
          FROM pictures p
          JOIN picture_votes pv
          ON p.picture_id = pv.picture_id
          GROUP BY p.picture_id,
                   p.category_id
          ORDER BY p.category_id, sumvotes DESC
          )as ppv,
     (SELECT @prevCat:=0,@voteOrder:=0)pc
     )finalTable
WHERE voteOrder BETWEEN 1 AND 3
ORDER BY category_id ASC, voteOrder ASC

Fetching top 3 from another table for every row in a 3rd table

3 Answers3