1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

select 5 rows from each group

Discussion in 'MySQL' started by divinequran, Jun 22, 2011.

  1. divinequran

    divinequran New Member

    Joined:
    Oct 9, 2008
    Messages:
    62
    Likes Received:
    1
    Trophy Points:
    0
    Occupation:
    Software Engineer
    Home Page:
    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?
     
  2. Webdeveloper

    Webdeveloper New Member

    Joined:
    Jun 22, 2011
    Messages:
    28
    Likes Received:
    1
    Trophy Points:
    0
    Occupation:
    IT Professional
    Location:
    New Delhi
    Home Page:
    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
     
  3. divinequran

    divinequran New Member

    Joined:
    Oct 9, 2008
    Messages:
    62
    Likes Received:
    1
    Trophy Points:
    0
    Occupation:
    Software Engineer
    Home Page:
    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.
     
  4. Webdeveloper

    Webdeveloper New Member

    Joined:
    Jun 22, 2011
    Messages:
    28
    Likes Received:
    1
    Trophy Points:
    0
    Occupation:
    IT Professional
    Location:
    New Delhi
    Home Page:
    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,
     
  5. divinequran

    divinequran New Member

    Joined:
    Oct 9, 2008
    Messages:
    62
    Likes Received:
    1
    Trophy Points:
    0
    Occupation:
    Software Engineer
    Home Page:
    Please can you help in writing the Mysql query
     

Share This Page