With the help of many comments, I could make a query for selecting top 2 score for each subject for each person.
SELECT t.*
FROM test_result AS t
WHERE t.id In (SELECT TOP 2 id
FROM test_result
WHERE student = t.student
AND subject = t.subject
ORDER BY score desc, id asc)
ORDER BY t.student asc, t.score asc;
This time I want to make this query in MYSQL.
Unfortunately, there isn't top syntax in MYSQL.
So, I searched the Internet and I finally get an answer like this:
set @num := 0, @type := '';
select type, variety, price
from (
select type, variety, price,
@num := if(@type = type, @num + 1, 1) as row_number,
@type := type as dummy
from fruits
order by type, price
) as x where x.row_number <= 2;
Can anyone help me how to add one more condition on this query?
I'm sorry that I wasn't clear enough. I want the result to be "top 2 prices of each variety of each type of fruits without using 'group by' clause."
