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!

SQL query

Discussion in 'SQL PL/SQL' started by gopikannan.oracle, May 24, 2011.

  1. What is the query to find second highest salary from the employee table?
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    What have you written to solve that problem? Please post your attempt.
     
  3. rajavu

    rajavu Forum Guru

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

    At least you could have tried in google before you post it here...

    google search
     
  4. anubasava

    anubasava Guest

    select rownum as Highest_sal_taken, last_name, salary
    from ( select last_name, salary
    from employees
    order by salary desc)
    where rownum <=3;

    you can try this..


    Thanks,
    Siddu
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Your query doesn't take into account duplicate salaries:

    Code (SQL):
    SQL> SELECT rownum AS Highest_sal_taken, last_name, salary
    2 FROM ( SELECT last_name, salary
    3 FROM employees
    4 ORDER BY salary DESC)
    5 WHERE rownum <=3;
     
    HIGHEST_SAL_TAKEN LAST_NAME SALARY
    ----------------- ------------------------- ----------
    1 King 24000
    2 Kochhar 17000
    3 De Haan 17000
     
    3 ROWS selected.
     
    SQL>
    The original poster wants to return the second highest salary from the table and your query doesn't do that. This is a better solution:

    Code (SQL):
    SQL> SELECT DISTINCT salary
    2 FROM
    3 (SELECT salary, dense_rank() OVER (ORDER BY salary DESC) rk
    4 FROM employees)
    5 WHERE rk=2;
     
    SALARY
    ----------
    17000
     
    1 ROW selected.
     
    SQL>
     
  6. robin

    robin Forum Advisor

    Messages:
    55
    Likes Received:
    7
    Trophy Points:
    160
    Location:
    Hyderabad
    This query also works:

    Select max(sal) from emp where sal <>(select max(sal) from emp);
     
  7. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You are correct it does return the correct value (however the table in use is the HR.EMPLOYEES table, not SCOTT.EMP). Is it not more efficient, though, to make one pass through the table rather than two? My query produces this execution plan:

    Code (SQL):
    Execution Plan
    ----------------------------------------------------------
    Plan hash VALUE: 2698234872
    --------------------------------------------------------------------------------------
    | Id  | Operation                | Name      | ROWS  | Bytes | Cost (%CPU)| TIME     |
    --------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT         |           |   107 |  2782 |     4  (25)| 00:00:01 |
    |*  1 |  VIEW                    |           |   107 |  2782 |     4  (25)| 00:00:01 |
    |*  2 |   WINDOW SORT PUSHED RANK|           |   107 |   428 |     4  (25)| 00:00:01 |
    |   3 |    TABLE ACCESS FULL     | EMPLOYEES |   107 |   428 |     3   (0)| 00:00:01 |
    --------------------------------------------------------------------------------------
    Predicate Information (IDENTIFIED BY operation id):
    ---------------------------------------------------
       1 - FILTER("RK"=2)
       2 - FILTER(DENSE_RANK() OVER ( ORDER BY INTERNAL_FUNCTION("SALARY") DESC
                  )<=2)
    Yours produces this plan:

    Code (SQL):
    Execution Plan
    ----------------------------------------------------------
    Plan hash VALUE: 1008057839
    ----------------------------------------------------------------------------------
    | Id  | Operation            | Name      | ROWS  | Bytes | Cost (%CPU)| TIME     |
    ----------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT     |           |     1 |     4 |     6   (0)| 00:00:01 |
    |   1 |  SORT AGGREGATE      |           |     1 |     4 |            |          |
    |*  2 |   TABLE ACCESS FULL  | EMPLOYEES |   105 |   420 |     3   (0)| 00:00:01 |
    |   3 |    SORT AGGREGATE    |           |     1 |     4 |            |          |
    |   4 |     TABLE ACCESS FULL| EMPLOYEES |   107 |   428 |     3   (0)| 00:00:01 |
    ----------------------------------------------------------------------------------
    Predicate Information (IDENTIFIED BY operation id):
    ---------------------------------------------------
       2 - FILTER("SALARY"<> (SELECT MAX("SALARY") FROM "EMPLOYEES"
                  "EMPLOYEES"))
     
    For small datasets the difference is minimal, however for thousands or millions of rows the analytic function query will likely outperform yours.

    A thought to consider.
     
    robin likes this.
  8. robin

    robin Forum Advisor

    Messages:
    55
    Likes Received:
    7
    Trophy Points:
    160
    Location:
    Hyderabad
    Hi Zargan,

    I am sorry but I didnot understand your Execution Plan.How did you calculate this.Please let me know how to do this


    Thanks in Advance,
    Robin
     
  9. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    It's a fairly easy act:

    Code (SQL):
    SET autotrace ON linesize 132
    SELECT salary FROM
    (SELECT salary, dense_rank() OVER (ORDER BY salary DESC) rk
            FROM employees)
    WHERE rk=2;
    SELECT MAX(salary) FROM employees WHERE salary <> (SELECT MAX(salary) FROM employees);
    SET autotrace off linesize 80
    which produces:

    Code (SQL):
    SQL> SET autotrace ON linesize 132
    SQL>
    SQL> SELECT salary FROM
      2  (SELECT salary, dense_rank() OVER (ORDER BY salary DESC) rk
      3          FROM employees)
      4  WHERE rk=2;
        SALARY
    ----------
         17000
         17000
    2 ROWS selected.

    Execution Plan
    ----------------------------------------------------------
    Plan hash VALUE: 2698234872
    --------------------------------------------------------------------------------------
    | Id  | Operation                | Name      | ROWS  | Bytes | Cost (%CPU)| TIME     |
    --------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT         |           |   107 |  2782 |     4  (25)| 00:00:01 |
    |*  1 |  VIEW                    |           |   107 |  2782 |     4  (25)| 00:00:01 |
    |*  2 |   WINDOW SORT PUSHED RANK|           |   107 |   428 |     4  (25)| 00:00:01 |
    |   3 |    TABLE ACCESS FULL     | EMPLOYEES |   107 |   428 |     3   (0)| 00:00:01 |
    --------------------------------------------------------------------------------------
    Predicate Information (IDENTIFIED BY operation id):
    ---------------------------------------------------
       1 - FILTER("RK"=2)
       2 - FILTER(DENSE_RANK() OVER ( ORDER BY INTERNAL_FUNCTION("SALARY") DESC
                  )<=2)

    Statistics
    ----------------------------------------------------------
              1  recursive calls
              0  db block gets
              7  consistent gets
              0  physical reads
              0  redo SIZE
            446  bytes sent via SQL*Net TO client
            385  bytes received via SQL*Net FROM client
              2  SQL*Net roundtrips TO/FROM client
              1  sorts (memory)
              0  sorts (disk)
              2  ROWS processed
    SQL>
    SQL> SELECT MAX(salary) FROM employees WHERE salary <> (SELECT MAX(salary) FROM employees);
    MAX(SALARY)
    -----------
          17000
    1 ROW selected.

    Execution Plan
    ----------------------------------------------------------
    Plan hash VALUE: 1008057839
    ----------------------------------------------------------------------------------
    | Id  | Operation            | Name      | ROWS  | Bytes | Cost (%CPU)| TIME     |
    ----------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT     |           |     1 |     4 |     6   (0)| 00:00:01 |
    |   1 |  SORT AGGREGATE      |           |     1 |     4 |            |          |
    |*  2 |   TABLE ACCESS FULL  | EMPLOYEES |   105 |   420 |     3   (0)| 00:00:01 |
    |   3 |    SORT AGGREGATE    |           |     1 |     4 |            |          |
    |   4 |     TABLE ACCESS FULL| EMPLOYEES |   107 |   428 |     3   (0)| 00:00:01 |
    ----------------------------------------------------------------------------------
    Predicate Information (IDENTIFIED BY operation id):
    ---------------------------------------------------
       2 - FILTER("SALARY"<> (SELECT MAX("SALARY") FROM "EMPLOYEES"
                  "EMPLOYEES"))

    Statistics
    ----------------------------------------------------------
              8  recursive calls
              0  db block gets
             16  consistent gets
              0  physical reads
              0  redo SIZE
            415  bytes sent via SQL*Net TO client
            385  bytes received via SQL*Net FROM client
              2  SQL*Net roundtrips TO/FROM client
              0  sorts (memory)
              0  sorts (disk)
              1  ROWS processed
    SQL>
    SQL> SET autotrace off linesize 80
    SQL>