This discussion is archived
1 2 Previous Next 26 Replies Latest reply: Apr 21, 2013 10:37 AM by Frank Kulash Go to original post RSS
  • 15. Re: query to retrieve the second highest managers salary
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    987184 wrote:
    hii,
    it worked . . can u write the query without using dense_rank() function.
    Sure; there are lots of other ways to get the same results. I suggested DENSE_RANK becuase it is the most efficient way, and also the simplest, and easiest to adapt to different requirements (e.g., if you need the 2nd highest salary from each job_id in the same query). Why would you want to use something slower, or more complicated, or harder to maintain, or all three?

    What's wrong with DENSE_RANK? As long as I don't know, I'm liable to suggest an alternative that doesn't suit you for the same reason, or is even worse.
  • 16. Re: query to retrieve the second highest managers salary
    John Stegeman Oracle ACE
    Currently Being Moderated
    teacher hasn't taught about analytics yet, so the op needs a solution that doesn't use them
  • 17. Re: query to retrieve the second highest managers salary
    990187 Newbie
    Currently Being Moderated
    hiii,
    i just tried many times and i have posted it after failing to do so. i want to do it without usingdense_rank(),rank() functions.please do help?
  • 18. Re: query to retrieve the second highest managers salary
    990187 Newbie
    Currently Being Moderated
    our teacher has asked to write without analytic function.dats y.
  • 19. Re: query to retrieve the second highest managers salary
    John Stegeman Oracle ACE
    Currently Being Moderated
    :0

    so it is homework.

    if we give the answer, do we get the grade too?
  • 20. Re: query to retrieve the second highest managers salary
    990187 Newbie
    Currently Being Moderated
    please try to understand,if u help me get the logic . . i will surely get the idea as well, so please do it without analytic function
  • 21. Re: query to retrieve the second highest managers salary
    Solomon Yakobson Guru
    Currently Being Moderated
    987184 wrote:
    our teacher has asked to write without analytic function.dats y.
    1. List of manager IDs:
    select  manager_id
      from  hr.employees
      where manager_id is not null
    /
    2. Manager info:
    select  employee_id,
            first_name,
            last_name,
            salary
      from  hr.employees
      where employee_id in (
                            select  manager_id
                              from  hr.employees
                              where manager_id is not null
                           )
    /
    3. Manager second highest salary
    with managers as (
                      select  employee_id,
                              first_name,
                              last_name,
                              salary
                        from  hr.employees
                        where employee_id in (
                                              select  manager_id
                                                from  hr.employees
                                                where manager_id is not null
                                             )
                     )
    select  m1.employee_id,
            m1.first_name,
            m1.last_name,
            max(m1.salary) second_highest_salary
      from  managers m1,
            managers m2
      where m1.salary < m2.salary
      group by m1.employee_id,
               m1.first_name,
               m1.last_name
      having count(distinct m2.salary) = 1
    /
    And execution:
    SQL> select  employee_id,
      2          first_name,
      3          last_name,
      4          salary
      5    from  hr.employees
      6    where employee_id in (
      7                          select  manager_id
      8                            from  hr.employees
      9                            where manager_id is not null
     10                         )
     11    order by salary desc
     12  /
    
    EMPLOYEE_ID FIRST_NAME           LAST_NAME                     SALARY
    ----------- -------------------- ------------------------- ----------
            100 Steven               King                           24000
            102 Lex                  De Haan                        17000
            101 Neena                Kochhar                        17000
            145 John                 Russell                        14000
            146 Karen                Partners                       13500
            201 Michael              Hartstein                      13000
            108 Nancy                Greenberg                      12008
            205 Shelley              Higgins                        12008
            147 Alberto              Errazuriz                      12000
            114 Den                  Raphaely                       11000
            148 Gerald               Cambrault                      11000
    
    EMPLOYEE_ID FIRST_NAME           LAST_NAME                     SALARY
    ----------- -------------------- ------------------------- ----------
            149 Eleni                Zlotkey                        10500
            103 Alexander            Hunold                          9000
            121 Adam                 Fripp                           8200
            120 Matthew              Weiss                           8000
            122 Payam                Kaufling                        7900
            123 Shanta               Vollman                         6500
            124 Kevin                Mourgos                         5800
    
    18 rows selected.
    
    SQL> with managers as (
      2                    select  employee_id,
      3                            first_name,
      4                            last_name,
      5                            salary
      6                      from  hr.employees
      7                      where employee_id in (
      8                                            select  manager_id
      9                                              from  hr.employees
     10                                              where manager_id is not null
     11                                           )
     12                   )
     13  select  m1.employee_id,
     14          m1.first_name,
     15          m1.last_name,
     16          max(m1.salary) second_highest_salary
     17    from  managers m1,
     18          managers m2
     19    where m1.salary < m2.salary
     20    group by m1.employee_id,
     21             m1.first_name,
     22             m1.last_name
     23    having count(distinct m2.salary) = 1
     24  /
    
    EMPLOYEE_ID FIRST_NAME           LAST_NAME                 SECOND_HIGHEST_SALARY
    ----------- -------------------- ------------------------- ---------------------
            101 Neena                Kochhar                                   17000
            102 Lex                  De Haan                                   17000
    
    SQL> 
    As you can see, highest salary of 24000 is earned by King. Second highest salary is 17000 and is earned by two managers: Kochhar and De Haan.

    SY.
  • 22. Re: query to retrieve the second highest managers salary
    John Stegeman Oracle ACE
    Currently Being Moderated
    Thank you Stephen.

    You're welcome, Calvin.
  • 23. Re: query to retrieve the second highest managers salary
    990187 Newbie
    Currently Being Moderated
    Thanks solomon,
    the logic was good but can u explain what does having count(distinct m2.salary) = 1 in the query do.
  • 24. Re: query to retrieve the second highest managers salary
    Solomon Yakobson Guru
    Currently Being Moderated
    987184 wrote:
    can u explain what does having count(distinct m2.salary) = 1 in the query do.
    We are self joining managers and for a given manager we are looking for all managers who have higher salary (where m1.salary < m2.salary). There can be N managers earning more but we can't say our manager has N+1st highest salary since some of the managers earning more than our manager can have same salary. So we need to count how many distinct salaries are earned by managers who have higher salary than our manager. And that's what having count(distinct m2.salary) does. And since we are looking for managers earning second highest salary, we use having count(distinct m2.salary) = 1 which means we select managers where there is only one higher salary comparing to what they earn.

    SY.
  • 25. Re: query to retrieve the second highest managers salary
    990187 Newbie
    Currently Being Moderated
    Hello solomon,

    can u just help me out getting the logic:can u write a sample program that matches the criteria below:

    an interviewer asked me:
    if i get an exception when i fire a query in a cursor or procedure and there are 1000 records in the table. if exception occurs then only records 200-250 will be saved and remaining will be rolledback.
  • 26. Re: query to retrieve the second highest managers salary
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    987184 wrote:
    Hello solomon,

    can u just help me out getting the logic:can u write a sample program that matches the criteria below:

    an interviewer asked me:
    if i get an exception when i fire a query in a cursor or procedure and there are 1000 records in the table. if exception occurs then only records 200-250 will be saved and remaining will be rolledback.
    This doesn't have anything to do with the 2nd highest salary, does it?
    If this is a completely separate question, then start a completely separate thread for it. Not many people are going to read a thread that already has 25 repliess. More people will notice a new thread, and you'll get replies sooner.

    When you COMMIT changes, you save all pending changes; there is no way to say "COMMIT only these rows". There is a way to say "don't do the most recent changes", however: that way is ROLLBACK TO SAVEPOINT, so you cannot save only rows 200-250; but you can save rows 1-51; so one way to do what you want is to change the numbering so that
    (a) rows 1-199 become rows 52-250,
    (b) rows 200-250 become rows 1-51
    (c) rows 251 and higher keep their original numbers
    There is no built-in order to rows in a relational database. If it makes sense to talk about rows 200-250, it is only in the context of some ordering, based on data in the table and/or imposed from outside. You have to define what that order is.
    Whatever the ordering is, you can use the analytic ROW_NUMBER function to assign consecutive integers (1, 2, 3, ...) to rows in that order. Let's call the number that ROW)_NUMBER returns r. Given r, you can derive a number called new_r, which changes the numbering as described above:
    CASE
        WHEN  r <  200  THEN  r +  51
        WHEN  r <= 250  THEN  r - 199
                        ELSE  r
    END   AS new_r
     

    I hope this answers your question.
    If not, start a new thread, and in it post the information specified in the forum FAQ {message:id=9360002}
1 2 Previous Next

Legend

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