1 2 Previous Next 26 Replies Latest reply: Apr 21, 2013 12:37 PM by Frank Kulash Go to original post RSS
      • 15. Re: query to retrieve the second highest managers salary
        Frank Kulash
        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
          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
            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
              our teacher has asked to write without analytic function.dats y.
              • 19. Re: query to retrieve the second highest managers salary
                John Stegeman
                :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
                  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
                    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
                      Thank you Stephen.

                      You're welcome, Calvin.
                      • 23. Re: query to retrieve the second highest managers salary
                        990187
                        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
                          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
                            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
                              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