CODEWITHSUNDEEP
mysqlgreatest-n-per-group
lookbadgers
lookbadgers

Reputation: 988

MySQL Greatest N Results with Join Tables

Selecting the Top n Results, I've seen the numerous posts and great articles on here about how to do it but I am struggling to do it with my data set. Most of the examples focus on data sets without the need for additional joins.

I've been trying to apply the examples from http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/ to my query without much success.

Three tables exist Person, Credit and Media.

Person links to Credit and Credit to Media.

The query below should return the top 5 medias per person, however it doesn't, where have I gone wrong?

SELECT 
p.id AS person_id, 
c.id AS credit_id, 
m.id AS media_id, m.rating_average
FROM person p
INNER JOIN credit c ON c.person_id = p.id
INNER JOIN media m ON m.id = c.media_id
where (
   select count(*) from media as m2
    inner JOIN credit c2 on m2.id=c2.media_id
   where c2.person_id = c.person_id and m2.rating_average >= m.rating_average
) <= 5

Clarification:

Top Medias are calculated from those with the highest rating_average.

Update:

SQLFiddle http://sqlfiddle.com/#!9/eb0fd

Desired output for top 3 medias (m) per person (p). Obviously I would like to do be able this for the top 5 medias but this is only test data.

p   m   c   rating_average
1   9   27  9
1   7   28  8
1   1   1   8
2   1   5   8
2   4   8   8
2   7   29  8
3   4   10  8
3   3   9   6
3   5   11  5
4   3   13  6
4   5   14  5
4   6   15  3
5   4   16  8
5   5   17  5
5   6   18  3
6   6   19  3
7   7   20  8
8   9   23  9
8   1   21  8
8   8   22  0
9   1   24  8
9   7   26  8
9   5   25  5

Upvotes: 1

Views: 109

Answers (1)

Aleksandar Miladinovic
Aleksandar Miladinovic

Reputation: 1027

i think i solve it :)

First here is one solution based on the way you started. But there is a catch I couldn't solve it to show exact 3 (or whatever number you choose i pick 3 for example) row for each person_id. Problem is that solution is based on counting how many rows is there with the rating_average greater then current row. So if you have 5 same top value you could choose to show all 5 or not to show them at all and that's not good. So here is the way you do that... (of course this is example where if you have 4 top value you show them all (I think that no make sense at all to not show the data))...

 SELECT t1.person_id, t1.credit_id, t1.media_id, t1.rating_average
 FROM (SELECT p.id AS person_id, c.id AS credit_id, m.id AS media_id, 
              m.rating_average AS rating_average
       FROM person p
       INNER JOIN credit c ON c.person_id = p.id
       INNER JOIN media m ON m.id = c.media_id) as t1
 WHERE (SELECT COUNT(*) 
       FROM (SELECT p.id AS person_id, c.id AS credit_id, m.id AS media_id, 
                    m.rating_average AS rating_average
             FROM person p
             INNER JOIN credit c ON c.person_id = p.id
             INNER JOIN media m ON m.id = c.media_id) AS t2
       WHERE t2.person_id = t1.person_id AND t2.rating_average > t1.rating_average) < 3
 ORDER BY person_id ASC, rating_average DESC

Important: This solution can work (to show exact 3 rows for each person) if you don't have value that repeat it self... Here is the Fiddle http://sqlfiddle.com/#!9/eb0fd/64 you can see the problem where person_id is 1!

After that i played a little more and make it work just as you wanted in the question i think. Here is a code for that:

SET @num := 0, @person := 0;

SELECT person_id, credit_id, media_id, rating_average, rowNumber 
FROM (SELECT t1.person_id, t1.credit_id, t1.media_id, t1.rating_average,
             @num := if(@person = t1.person_id, @num + 1, 1) AS rowNumber,
             @person := t1.person_id
      FROM (SELECT p.id AS person_id, c.id AS credit_id, m.id AS media_id, 
                   m.rating_average AS rating_average
            FROM person p
            INNER JOIN credit c ON c.person_id = p.id
            INNER JOIN media m ON m.id = c.media_id
            ORDER BY p.id ASC, m.rating_average DESC) as t1) as t2
 WHERE rowNumber <= 3

Here is the Fiddle for that http://sqlfiddle.com/#!9/eb0fd/65 ...

GL!

P. S. sorry for my English hope you could understand what i was talking about...

Upvotes: 1

Related Questions