select 5 rows from each group

divinequran's Avatar, Join Date: Oct 2008
Contributor
I have a big student database, it has only one table. I want to select 10 student each department on a single mysql query.

+---------------------------------------------------------+----------------------+
| student | Dept |
+---------------------------------------------------------+----------------------+
| gooch | physics |
| aaaaa | mathss |
| dvssdg | maths |
| sfgsfg | chemistry |
| gcbcvb | chemistry |
| dgsgsfg | arch |

I have a big list like this.. I have to select ten student from each department. how shall I achieve it?
Webdeveloper's Avatar, Join Date: Jun 2011
Go4Expert Member
Hi,

You can do this using some scripting and the logic will be: -

~ First fetch the distinct names of all the departments
~ Run a loop for each department and pick top ten records from each record set

Hope this helps.

Cheers,

~Maneet
divinequran's Avatar, Join Date: Oct 2008
Contributor
Say, If I have 100 department then the loop will run for 100 times and sql statement will run for 100 times. I dont want it to run the 100 times I just want it to happen only once.
Webdeveloper's Avatar, Join Date: Jun 2011
Go4Expert Member
The only way to do it in one go is by a query you will have to split the table into two so that u can write one query with joins to fetch it with one query.

Hope this helps,
divinequran's Avatar, Join Date: Oct 2008
Contributor
Please can you help in writing the Mysql query