How to limit using group by in mysql -

How to limit using group by in mysql -

i have big problem. don't know how limit using grouping by. below have 12 set of students. want limit 10 set of students. want remove lastly 2 @ bottom. when seek utilize limit 10 returns 10 rows. tried search , tried code below:

select vsg.student_id , vsg.school_year_id , vsg.level_id , vsg.fname , vsg.lname , vsg.mname , vsg.subject , vsg.grade , (select avg(vsg.grade) v_student_grades student_id = vsg.student_id ) avg_grade v_student_grades vsg bring together ( select * ( select student_id, avg(grade) avg_grade v_student_grades grouping student_id ) sg order avg_grade desc limit 10 ) vg on vg.student_id=vsg.student_id school_year_id = 2 , level_id = 7 , period_id =1 , subject_id=subject_id grouping student_id, subject order avg_grade desc, subject asc;

i have error:

error code: 1242. subquery returns more 1 row

below table 12 sets of students. want remove set of students have student_id 46 , 8.

please help. thanks.

+------------+----------------+----------+-----------+------------------------+-------------+--------------------+-------------+-------+-------------------+ | student_id | school_year_id | level_id | period_id | fname | lname | mname | subject | grade | avg_grade | +------------+----------------+----------+-----------+------------------------+-------------+--------------------+-------------+-------+-------------------+ | 4 | 2 | 7 | 1 | asd | qwerty | zxc | math 100 | 97 | 90.66666666666667 | | 4 | 2 | 7 | 1 | asd | qwerty | zxc | crim 111 | 85 | 90.66666666666667 | | 4 | 2 | 7 | 1 | asd | qwerty | zxc | biological science | 98 | 90.66666666666667 | | 4 | 2 | 7 | 1 | asd | qwerty | zxc | elective 1 | 95 | 90.66666666666667 | | 4 | 2 | 7 | 1 | asd | qwerty | zxc | civil 111 | 78 | 90.66666666666667 | | 4 | 2 | 7 | 1 | asd | qwerty | zxc | english language 222 | 96 | 90.66666666666667 | | 4 | 2 | 7 | 1 | asd | qwerty | zxc | computer | 82 | 90.66666666666667 | | 4 | 2 | 7 | 1 | asd | qwerty | zxc | mapeh | 92 | 90.66666666666667 | | 4 | 2 | 7 | 1 | asd | qwerty | zxc | consti 222 | 93 | 90.66666666666667 | | 34 | 2 | 7 | 1 | edeline | teh | s | mapeh | 95 | 90 | | 34 | 2 | 7 | 1 | edeline | teh | s | consti 222 | 84 | 90 | | 34 | 2 | 7 | 1 | edeline | teh | s | math 100 | 97 | 90 | | 34 | 2 | 7 | 1 | edeline | teh | s | crim 111 | 87 | 90 | | 34 | 2 | 7 | 1 | edeline | teh | s | biological science | 90 | 90 | | 34 | 2 | 7 | 1 | edeline | teh | s | elective 1 | 93 | 90 | | 34 | 2 | 7 | 1 | edeline | teh | s | civil 111 | 84 | 90 | | 34 | 2 | 7 | 1 | edeline | teh | s | english language 222 | 98 | 90 | | 34 | 2 | 7 | 1 | edeline | teh | s | computer | 82 | 90 | | 31 | 2 | 7 | 1 | john farid | macaludos | s | math 100 | 74 | 89.55555555555556 | | 31 | 2 | 7 | 1 | john farid | macaludos | s | crim 111 | 89 | 89.55555555555556 | | 31 | 2 | 7 | 1 | john farid | macaludos | s | biological science | 89 | 89.55555555555556 | | 31 | 2 | 7 | 1 | john farid | macaludos | s | elective 1 | 96 | 89.55555555555556 | | 31 | 2 | 7 | 1 | john farid | macaludos | s | civil 111 | 95 | 89.55555555555556 | | 31 | 2 | 7 | 1 | john farid | macaludos | s | english language 222 | 98 | 89.55555555555556 | | 31 | 2 | 7 | 1 | john farid | macaludos | s | computer | 87 | 89.55555555555556 | | 31 | 2 | 7 | 1 | john farid | macaludos | s | mapeh | 96 | 89.55555555555556 | | 31 | 2 | 7 | 1 | john farid | macaludos | s | consti 222 | 82 | 89.55555555555556 | | 15 | 2 | 7 | 1 | dealson | bagorio | s | mapeh | 98 | 88.77777777777777 | | 15 | 2 | 7 | 1 | dealson | bagorio | s | consti 222 | 83 | 88.77777777777777 | | 15 | 2 | 7 | 1 | dealson | bagorio | s | math 100 | 80 | 88.77777777777777 | | 15 | 2 | 7 | 1 | dealson | bagorio | s | crim 111 | 99 | 88.77777777777777 | | 15 | 2 | 7 | 1 | dealson | bagorio | s | biological science | 98 | 88.77777777777777 | | 15 | 2 | 7 | 1 | dealson | bagorio | s | elective 1 | 96 | 88.77777777777777 | | 15 | 2 | 7 | 1 | dealson | bagorio | s | civil 111 | 96 | 88.77777777777777 | | 15 | 2 | 7 | 1 | dealson | bagorio | s | english language 222 | 75 | 88.77777777777777 | | 15 | 2 | 7 | 1 | dealson | bagorio | s | computer | 74 | 88.77777777777777 | | 6 | 2 | 7 | 1 | gintoki | sakata | g | civil 111 | 71 | 88.55555555555556 | | 6 | 2 | 7 | 1 | gintoki | sakata | g | english language 222 | 98 | 88.55555555555556 | | 6 | 2 | 7 | 1 | gintoki | sakata | g | computer | 74 | 88.55555555555556 | | 6 | 2 | 7 | 1 | gintoki | sakata | g | mapeh | 92 | 88.55555555555556 | | 6 | 2 | 7 | 1 | gintoki | sakata | g | consti 222 | 95 | 88.55555555555556 | | 6 | 2 | 7 | 1 | gintoki | sakata | g | math 100 | 99 | 88.55555555555556 | | 6 | 2 | 7 | 1 | gintoki | sakata | g | crim 111 | 85 | 88.55555555555556 | | 6 | 2 | 7 | 1 | gintoki | sakata | g | biological science | 98 | 88.55555555555556 | | 6 | 2 | 7 | 1 | gintoki | sakata | g | elective 1 | 85 | 88.55555555555556 | | 7 | 2 | 7 | 1 | officedump | superschool | accountsreceivable | elective 1 | 84 | 88.33333333333333 | | 7 | 2 | 7 | 1 | officedump | superschool | accountsreceivable | civil 111 | 73 | 88.33333333333333 | | 7 | 2 | 7 | 1 | officedump | superschool | accountsreceivable | english language 222 | 98 | 88.33333333333333 | | 7 | 2 | 7 | 1 | officedump | superschool | accountsreceivable | computer | 72 | 88.33333333333333 | | 7 | 2 | 7 | 1 | officedump | superschool | accountsreceivable | mapeh | 93 | 88.33333333333333 | | 7 | 2 | 7 | 1 | officedump | superschool | accountsreceivable | consti 222 | 92 | 88.33333333333333 | | 7 | 2 | 7 | 1 | officedump | superschool | accountsreceivable | math 100 | 98 | 88.33333333333333 | | 7 | 2 | 7 | 1 | officedump | superschool | accountsreceivable | crim 111 | 98 | 88.33333333333333 | | 7 | 2 | 7 | 1 | officedump | superschool | accountsreceivable | biological science | 87 | 88.33333333333333 | | 21 | 2 | 7 | 1 | nivea | gegone | m | crim 111 | 87 | 88.22222222222223 | | 21 | 2 | 7 | 1 | nivea | gegone | m | biological science | 89 | 88.22222222222223 | | 21 | 2 | 7 | 1 | nivea | gegone | m | elective 1 | 98 | 88.22222222222223 | | 21 | 2 | 7 | 1 | nivea | gegone | m | civil 111 | 95 | 88.22222222222223 | | 21 | 2 | 7 | 1 | nivea | gegone | m | english language 222 | 98 | 88.22222222222223 | | 21 | 2 | 7 | 1 | nivea | gegone | m | computer | 72 | 88.22222222222223 | | 21 | 2 | 7 | 1 | nivea | gegone | m | mapeh | 98 | 88.22222222222223 | | 21 | 2 | 7 | 1 | nivea | gegone | m | consti 222 | 82 | 88.22222222222223 | | 21 | 2 | 7 | 1 | nivea | gegone | m | math 100 | 75 | 88.22222222222223 | | 13 | 2 | 7 | 1 | jasima | alonto | d | english language 222 | 82 | 87.55555555555556 | | 13 | 2 | 7 | 1 | jasima | alonto | d | computer | 83 | 87.55555555555556 | | 13 | 2 | 7 | 1 | jasima | alonto | d | mapeh | 98 | 87.55555555555556 | | 13 | 2 | 7 | 1 | jasima | alonto | d | consti 222 | 94 | 87.55555555555556 | | 13 | 2 | 7 | 1 | jasima | alonto | d | math 100 | 100 | 87.55555555555556 | | 13 | 2 | 7 | 1 | jasima | alonto | d | crim 111 | 72 | 87.55555555555556 | | 13 | 2 | 7 | 1 | jasima | alonto | d | biological science | 87 | 87.55555555555556 | | 13 | 2 | 7 | 1 | jasima | alonto | d | elective 1 | 79 | 87.55555555555556 | | 13 | 2 | 7 | 1 | jasima | alonto | d | civil 111 | 93 | 87.55555555555556 | | 12 | 2 | 7 | 1 | roderick | almero | | mapeh | 92 | 87.22222222222223 | | 12 | 2 | 7 | 1 | roderick | almero | | consti 222 | 96 | 87.22222222222223 | | 12 | 2 | 7 | 1 | roderick | almero | | math 100 | 100 | 87.22222222222223 | | 12 | 2 | 7 | 1 | roderick | almero | | crim 111 | 79 | 87.22222222222223 | | 12 | 2 | 7 | 1 | roderick | almero | | biological science | 87 | 87.22222222222223 | | 12 | 2 | 7 | 1 | roderick | almero | | elective 1 | 74 | 87.22222222222223 | | 12 | 2 | 7 | 1 | roderick | almero | | civil 111 | 75 | 87.22222222222223 | | 12 | 2 | 7 | 1 | roderick | almero | | english language 222 | 100 | 87.22222222222223 | | 12 | 2 | 7 | 1 | roderick | almero | | computer | 82 | 87.22222222222223 | | 10 | 2 | 7 | 1 | ma. lourdes antoinnete | 3 roses | de asis | biological science | 78 | 86.88888888888889 | | 10 | 2 | 7 | 1 | ma. lourdes antoinnete | 3 roses | de asis | elective 1 | 92 | 86.88888888888889 | | 10 | 2 | 7 | 1 | ma. lourdes antoinnete | 3 roses | de asis | civil 111 | 94 | 86.88888888888889 | | 10 | 2 | 7 | 1 | ma. lourdes antoinnete | 3 roses | de asis | english language 222 | 97 | 86.88888888888889 | | 10 | 2 | 7 | 1 | ma. lourdes antoinnete | 3 roses | de asis | computer | 75 | 86.88888888888889 | | 10 | 2 | 7 | 1 | ma. lourdes antoinnete | 3 roses | de asis | mapeh | 96 | 86.88888888888889 | | 10 | 2 | 7 | 1 | ma. lourdes antoinnete | 3 roses | de asis | consti 222 | 83 | 86.88888888888889 | | 10 | 2 | 7 | 1 | ma. lourdes antoinnete | 3 roses | de asis | math 100 | 84 | 86.88888888888889 | | 10 | 2 | 7 | 1 | ma. lourdes antoinnete | 3 roses | de asis | crim 111 | 83 | 86.88888888888889 | | 46 | 2 | 7 | 1 | archi | masa | | english language 222 | 81 | 86.22222222222223 | | 46 | 2 | 7 | 1 | archi | masa | | computer | 82 | 86.22222222222223 | | 46 | 2 | 7 | 1 | archi | masa | | mapeh | 99 | 86.22222222222223 | | 46 | 2 | 7 | 1 | archi | masa | | consti 222 | 92 | 86.22222222222223 | | 46 | 2 | 7 | 1 | archi | masa | | math 100 | 100 | 86.22222222222223 | | 46 | 2 | 7 | 1 | archi | masa | | crim 111 | 75 | 86.22222222222223 | | 46 | 2 | 7 | 1 | archi | masa | | biological science | 86 | 86.22222222222223 | | 46 | 2 | 7 | 1 | archi | masa | | elective 1 | 76 | 86.22222222222223 | | 46 | 2 | 7 | 1 | archi | masa | | civil 111 | 85 | 86.22222222222223 | | 8 | 2 | 7 | 1 | fn | ln | mn | consti 222 | 94 | 82.88888888888889 | | 8 | 2 | 7 | 1 | fn | ln | mn | math 100 | 100 | 82.88888888888889 | | 8 | 2 | 7 | 1 | fn | ln | mn | crim 111 | 78 | 82.88888888888889 | | 8 | 2 | 7 | 1 | fn | ln | mn | biological science | 78 | 82.88888888888889 | | 8 | 2 | 7 | 1 | fn | ln | mn | elective 1 | 82 | 82.88888888888889 | | 8 | 2 | 7 | 1 | fn | ln | mn | civil 111 | 76 | 82.88888888888889 | | 8 | 2 | 7 | 1 | fn | ln | mn | english language 222 | 65 | 82.88888888888889 | | 8 | 2 | 7 | 1 | fn | ln | mn | computer | 79 | 82.88888888888889 | | 8 | 2 | 7 | 1 | fn | ln | mn | mapeh | 94 | 82.88888888888889 | +------------+----------------+----------+-----------+------------------------+-------------+--------------------+-------------+-------+-------------------+

try one.

...... grouping student_id in (select distinct student_id tablename limit 10), subject order avg_grade desc, subject asc;

i think help you.

mysql