How to get Max top 3 sal from emp table
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: How to get Max top 3 sal from emp table

  1. #1
    Join Date
    Nov 2001
    Location
    Delhi
    Posts
    31
    Dear Friend,
    I want to fetch max top three salary from our emp table which having different different sal.For example
    name sal
    john 4000
    fred 3000
    joseph 4500
    xyz 4300
    abc 6000

    Our result should return only abc,joseph and xyz
    So please tell me how to write query for this
    Thanks
    Imtiaz
    Imti
    Junior DBA

  2. #2
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    select name, sal from
    (select name, sal from emp order by sal desc)
    where rownum<=3

  3. #3
    Join Date
    Nov 2001
    Location
    Delhi
    Posts
    31
    Thanks for reply but my requirent is like this only top 3 result should not be repeated for example

    name sal
    john 4000
    joseph 4500
    abc 4500
    xyz 5000
    abcl 4200


    I want result only xyz,abc9any should not repeat) and abcl
    Imti
    Junior DBA

  4. #4
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    Yes, you want to show just distinct salaries but still with names.
    You want to see xyz, abc and abcl with their salaries.
    My question is: Why do you want to see abc and not joseph? How to decide which emp show and which not in case of same salaries?

  5. #5
    Join Date
    May 2002
    Posts
    108

    Thumbs up Try this !

    Hi

    Query below can be a answer for your question !

    Select max(name), sal from emp
    group by sal
    order by sal desc

    I don't know how significant 'name' is going
    to be in the output, but use max(), min()
    function with name accordingly, if it is
    just a matter of finding largest or smallest alphabet?!!

    Cheers,
    Nandu


    Never give up !

    Nanda Kumar - Vellore

  6. #6
    Join Date
    May 2002
    Posts
    108

    I missed out a bit !

    Sorry I missed out a bit ! It is infact as follows...

    select * from (select max(name), sal from t_emp
    group by sal order by sal desc)
    where rownum < 4

    Cheers
    Nandu


    Never give up !

    Nanda Kumar - Vellore

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width