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;