select 5 rows from each group
 

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?

Re: select 5 rows from each group
 

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

Re: select 5 rows from each group
 

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.

Re: select 5 rows from each group
 

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,

Re: select 5 rows from each group
 

Please can you help in writing the Mysql query