Go4Expert

Go4Expert (http://www.go4expert.com/)
-   MySQL (http://www.go4expert.com/forums/mysql-forum/)
-   -   select 5 rows from each group (http://www.go4expert.com/forums/select-5-rows-t26115/)

divinequran 22Jun2011 11:22

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?

Webdeveloper 22Jun2011 17:13

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

divinequran 22Jun2011 23:34

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.

Webdeveloper 23Jun2011 16:06

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,

divinequran 25Jun2011 21:10

Re: select 5 rows from each group
 
Please can you help in writing the Mysql query


All times are GMT +5.5. The time now is 21:56.