Top x rows and group by (again)

Question

Hello,

I know it's a frequent question but I just can't figure it out and the examples I found didn't helped. What I learned, the best strategy is to try to find the top and bottom values of the top range and then select the rest, but implementing is a bit tricky.

Example table:

id | title | group_id | votes

---

I'd like to get the top 3 voted rows from the table, for each group.

I'm expecting this result:

91 | hello1 | 1 | 10 
28 | hello2 | 1 | 9
73 | hello3 | 1 | 8 
84 | hello4 | 2 | 456
58 | hello5 | 2 | 11 
56 | hello6 | 2 | 0 
17 | hello7 | 3 | 50
78 | hello8 | 3 | 9 
99 | hello9 | 3 | 1 

I've fond complex queries and examples, but they didn't really helped.

Answer 1

You can do it using variables:

SELECT
   id,
   title,
   group_id,
   votes
FROM (
    SELECT
        id,
        title,
        group_id,
        votes,
        @rn := CASE WHEN @prev = group_id THEN @rn + 1 ELSE 1 END AS rn,
        @prev := group_id
    FROM table1, (SELECT @prev := -1, @rn := 0) AS vars
    ORDER BY group_id DESC, votes DESC
) T1
WHERE rn <= 3
ORDER BY group_id, votes DESC

This is basically just the same as the following query in databases that support ROW_NUMBER:

SELECT
   id,
   title,
   group_id,
   votes
FROM (
     SELECT
        id,
        title,
        group_id,
        votes,
        ROW_NUMBER() OVER (PARTITION BY group_id ORDER BY votes DESC) AS rn
     FROM student
) T1
WHERE rn <= 3
ORDER BY group_id, votes DESC

But since MySQL doesn't support ROW_NUMBER yet you have to simulate it, and that's what the variables are for. The two queries are otherwise identical. Try to understand the second query first, and hopefully the first should make more sense.