mysql – I want to get the standard deviation and average value for the top of each group

Question: Question:

select avg(point) from table where (genre毎のpoint上位100位まで) GROUP BY genre;

I want to do something like this, what should I do?
I could only think of making a table with the top points …

Answer: Answer:

At first I tried to process with IN operator and subquery, but the following error message is displayed, so

MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery

I tried using user-defined variables and subqueries.

SET @num := 0, @genre := '';
SELECT t.genre, avg(t.point) AS average
FROM (
  SELECT t2.genre, t2.point,
    @num := IF(@genre = t2.genre, @num + 1, 1) AS rownum,
    @genre := t2.genre
  FROM test2 AS t2
  ORDER BY t2.genre ASC, t2.point DESC
) AS t WHERE t.rownum <= 100 GROUP BY t.genre;