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 …
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;