1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

how to select top 5 for each value in second field

Discussion in 'SQL PL/SQL' started by guddu2907, Jan 25, 2009.

  1. guddu2907

    guddu2907 Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    I have a table which contains salesmen ID and quantity sold by the salesman on a day basis. To prepare salesman performance report, I need to pickup top 5 numbers from quantity sold field for each of the salesman. What is the best way to do it?

    Thanks
    Guddu
     
  2. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    hi guddu, thanks for posting but it would be better if you at least provided the table structure. This looks like a homework question and it's not a good idea to seek direct easy answers. At least create the table try to write the query and if it doesn't work you can ask us to help.
     
  3. guddu2907

    guddu2907 Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    any way thanks...
     
  4. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
  5. prithviraaj

    prithviraaj Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Hi Guddu
    This query will work. Please confirm. In this type of queries order by clause is most important.
    Code (Text):

    SELECT Salesman_Id, Quantity  FROM
        (
           SELECT Salesman_Id, Quantity FROM Sales  ORDER BY Quantity desc
        ) WHERE  <= 5;
     
    Regards,
    Prithviraaj.
     
  6. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Prithviraaj , Sorry to say .. it wont work .

    It wont serve OP's need.

    Still Analytic function is the best option.
     
  7. anas_almasri

    anas_almasri Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    hello mr. rajavu:

    do you now about database performance when
    i use the analytical functions within the code

    thanks for you information about analytical function
     
  8. anas_almasri

    anas_almasri Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    why the analytical function
    don't work on forms pl/sql
    can you explain that
     
  9. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Sorry I didn't know that It wont work in Oracle Forms.
    Sorry I didn't know that It will slow down SQL .

    We will recommend Oracle not to use them i the future releases.