This discussion is archived
1 2 Previous Next 26 Replies Latest reply: Apr 21, 2013 10:37 AM by Frank Kulash RSS

query to retrieve the second highest managers salary

990187 Newbie
Currently Being Moderated
select * from employees where salary=(select max(salary) from employees e where employee_id=e.manager_id and salary<(select max(salary) from employees)
/

this is not executing . . .can anyone suggest new one
  • 1. Re: query to retrieve the second highest managers salary
    John Stegeman Oracle ACE
    Currently Being Moderated
    Homework? Interview question?

    What if the managers' salaries are 1000, 1000, 900, 900, 800, 700? What do you want returned?
  • 2. Re: query to retrieve the second highest managers salary
    990187 Newbie
    Currently Being Moderated
    hello,
    if there are two salaries are same,then both have to displayed.
  • 3. DENSE_RANK
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    987184 wrote:
    hello,
    if there are two salaries are same,then both have to displayed.
    This sounds like a job for the analytic DENSE_RANK function.

    If the top salaries are 1000, 1000, 1000, 900 and 900, then you're considering 900 to be the 2nd highest, right? Use DENSE_RANK for that.
    Like all analytic functions, DENSE_RANK is computed after the WHERE clause has been applied, so to use the results of DENSE_RANK in a WHERE clause, you have to call DENSE_RANK in a sub-query, and use the number it returns in the WHERE clause of a super-query.

    If you'd like help, post your best attempt, along with a little sample data (CREATE TABLE and INSERT statements), and the reults you want from that data.
    See the forum FAQ {message:id=9360002}
  • 4. Re: query to retrieve the second highest managers salary
    John Stegeman Oracle ACE
    Currently Being Moderated
    Since you're doing homework, you might as well learn this:

    The only answer to your original question "retrieve the second highest managers salary" as stated is "please provide a proper specification"

    "if there are two salaries are same,then both have to displayed" doesn't yet cut it.

    In the example I gave, what is the second highest manager's salary? Is it 1000? Is it 900?

    Once you can provide a proper specification, then someone here might be willing to help you with your homework.
  • 5. Re: query to retrieve the second highest managers salary
    John Stegeman Oracle ACE
    Currently Being Moderated
    This sounds like a job for the analytic DENSE_RANK function.
    Maybe, but until the OP asks a question that is unambiguous, we shall never know :)
  • 6. Re: query to retrieve the second highest managers salary
    990187 Newbie
    Currently Being Moderated
    hiii,
    what i mean is. if the managers salary is 10000,10000,2000,45000. then 2000 should be the answer.can u help me out with the query.
  • 7. Re: query to retrieve the second highest managers salary
    John Stegeman Oracle ACE
    Currently Being Moderated
    no, I can't help because you are unwilling and/or unable to give a proper, unambiguous question
  • 8. Re: query to retrieve the second highest managers salary
    APC Oracle ACE
    Currently Being Moderated
    You really need to pay more attention. 2000 is the lowest salary in that lot. Presumably you meant 20000.

    Cheers, APC
  • 9. Re: query to retrieve the second highest managers salary
    APC Oracle ACE
    Currently Being Moderated
    Anyway
    select salary from (
        ( select salary
                   , dense_rank() over (order by salary) as sal_rank
          from employees
          where job = 'MANAGER'
        )
    where sal_rank = 2
    Cheers, APC
  • 10. Re: query to retrieve the second highest managers salary
    990187 Newbie
    Currently Being Moderated
    hiii,
    iam sorry . . if the managers salary is 400000,400000,20000,2000,3400. . .. then 20000 should be returned. hope this is fine . . .
  • 11. Re: query to retrieve the second highest managers salary
    990187 Newbie
    Currently Being Moderated
    hii,
    it is displaying as invalid character at sal_rank=2

    select salary from (
    ( select salary
    , dense_rank() over (order by salary) as sal_rank
    from employees
    where job_id = 'PU_CLERK'
    )
    where sal_rank = 2;
    /
  • 12. Re: query to retrieve the second highest managers salary
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    987184 wrote:
    hii,
    it is displaying as invalid character at sal_rank=2

    select salary from (
    ( select salary
    , dense_rank() over (order by salary) as sal_rank
    from employees
    where job_id = 'PU_CLERK'
    )
    where sal_rank = 2;
    /
    You have unbalanced parentheses. You have 4 left '('s, but only 3 right ')'s


    You may have noticed that this site normally doesn't display multiple spaces in a row.
    Whenever you post formatted text (such as query results, as well as code) on this site, type these 6 characters:

    \
    (small letters only, inside curly brackets) before and after each section of formatted text, to preserve spacing.
    
    Why do you end the statement with a ';', and then a '/'?  Won;t either of them do what you want, without the other?
    
     
    
    Remember that the default sorting order is <b>ASC</b>ending (smallest first).  In this problem, don't you want <b>DESC</b>ending order (largest first)?                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
  • 13. Re: query to retrieve the second highest managers salary
    990187 Newbie
    Currently Being Moderated
    hii,
    it worked . . can u write the query without using dense_rank() function.
  • 14. Re: query to retrieve the second highest managers salary
    John Stegeman Oracle ACE
    Currently Being Moderated
    You still haven't grasped the concept I'm trying to put forth.

    What happens if there is only one manager?
    What if all of the managers make the same salary?

    You haven't thought about the question enough to be able to even begin to answer it.
    can u write the query without using dense_rank() function.
    Who is "u?"

    Yes, in general, such types of queries can be written without using rank() or dense_rank().

    If you were to sit down and actually write out the question as a specification, I think you can probably come up with a way to answer it. I myself refuse to give an answer because you seem to be refusing to:

    * think about the problem clearly
    * make some attempts on your own
    * show anything that indicates you're trying to understand the possible solutions provided and adapt them to your "requirement"

    You seem to be saying "shaddup and gimme the answer" and that's not the name of the game for me.

    Good luck with your homework.
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points