Select groups with the three biggest values

Question

Consider this data:

---+-----------+-------+--+
| id |   name    | grade |  |
+----+-----------+-------+--+
| 13 | Maria     |    10 |  |
| 18 | Lorenzo   |    10 |  |
|  2 | Cissa     |    10 |  |
|  3 | Neto      |     9 |  |
| 15 | Gabriel   |     9 |  |
| 10 | Laura     |     9 |  |
| 12 | Joãozinho |     8 |  |
| 16 | Sergio    |     8 |  |
|  8 | Adriele   |     8 |  |
|  6 | Jorgito   |     8 |  |
|  5 | Aline     |     8 |  |
|  1 | Cintia    |     8 |  |
| 19 | Fabiana   |     7 |  |
| 11 | Vinicius  |     7 |  |
|  9 | Tatiane   |     7 |  |
|  7 | Chico     |     7 |  |
|  4 | Marcos    |     7 |  |
| 14 | Pedro     |     6 |  |
| 17 | Mauricio  |     6 |  |
| 20 | Leonardo  |     6 |  |
+----+-----------+-------+--+

I need the students with the three biggest grades. I think I need to group the data by grade and limit to the top 3 groups.

"SELECT * FROM student GROUP BY grade LIMIT 3" only gives me 3 rows, that's not what I want.

I've tried to use HAVING to filter the groups, but without success. I don't want to set the filter grade>MAX(grade)-2, because theoretically I will not know the grades. But this filter didn't work anyway.

I'am using MySQL. Please help!

Answer 1

You can do this using a join:

select s.*
from student s join
     (select grade
      from student
      group by grade
      order by grade desc
      limit 3
     ) g3
     on s.grade = g3.grade;

In most databases, you an do this using in:

select s.*
from student s
where s.grade in (select grade
                  from student
                  group by grade
                  order by grade desc
                  limit 3
                 );

However, MySQL seems to reject this syntax.