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 to find fifth highest salary with empno

Discussion in 'SQL PL/SQL' started by sandip250382, Jun 29, 2010.

  1. sandip250382

    sandip250382 Active Member

    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    80
    A table namely emp has two fields, empno and salary. There are total 20 tuples(records ) in the table. Salary can be same for more than one employee. I want to know the query which can give the fifth highest salary along with empno. Kindly let me know the exact query. Thanks in Advance
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Re: Help me to solve this query

    And it appears there is an instructor who gave you this assignment to reinforce the classroom material. Having one of us simply give you the answer teaches you nothing.

    Post the work you've done to solve this problem and one of us can assist you.
     
  3. sandip250382

    sandip250382 Active Member

    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    80
    Re: Help me to solve this query

    I am in professional world so no classroom assignment to be forced on me . I am using rownum pseudo column for this query but I am getting top 5 salaries not the 5th highest salary. What will be the query for it ?
     
  4. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    Re: Help me to solve this query

    Hi sandip, please post the create table, sample insert statements and the query which you have written which is not giving the desired result. I guarantee you that we will solve your problem.
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Re: Help me to solve this query

    You can't use ROWNUM to solve this query if you have multiple employees with the same salary; the EMP table is a demonstration table and is used for classrom study, hence my comment. As Sadik just replied post your create table statement, insert statements for sample data and the query you've written to solve this and someone will help you.
     
  6. sandip250382

    sandip250382 Active Member

    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    80
    Re: Help me to solve this query

    Sorry for being a bit late in my reply.
    I have used the code :-
    create table emp(empno varchar2(30) primary key not null, salary number(10,2));
    insert into emp values('01',20000);
    insert into emp values('02',20000);
    insert into emp values('03',10000);
    insert into emp values('04',1239);
    insert into emp values('05',1211);
    insert into emp values('06',18900);
    insert into emp values('07',26000);
    insert into emp values('08',12991);
    insert into emp values('09',9812);
    insert into emp values('10',6701);
    insert into emp values('11',9012);
    insert into emp values ('12',8800);
    insert into emp values('13',19000);

    I am required to find out the fifth highest salary for it using SQL select statements.
    Kindly let me know asap .
     
  7. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Re: Help me to solve this query

    Where is the query you've written to try to solve this? An employer would not ask an employee to query the EMP demonstration table so I truly feel that this is homework or a classroom assignment.
     
  8. sandip250382

    sandip250382 Active Member

    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    80
    Re: Help me to solve this query

    I have written this Select Rownum as position, salary from (Select empno,salary from emp order by salary desc)
    where rownum < = 5

    I am able to get top 5 earners in this way, But I want to know empno,salary of the 5th highest employee .Kindly mention the changes needed to be done in this query to get the desired Output.
     
  9. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Re: Help me to solve this query

    Hi Sandip,
    As Zargon said we can write query in a minute and you can get answer. but you must put your hands on.

    I can give u the hint, if you are using rownum <= 5, then you will get 1,2,3,4 and 5. thats all , correct it.
     
  10. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Re: Help me to solve this query

    You get the top 4 'earners' that way since two employees have the same salary:

    Code (SQL):
     
    SQL> --
    SQL> -- Create table
    SQL> --
    SQL> CREATE TABLE emp(empno varchar2(30) PRIMARY KEY NOT NULL, salary NUMBER(10,2));
    TABLE created.
    SQL>
    SQL> --
    SQL> -- Insert data the long and tedious way
    SQL> --
    SQL> INSERT INTO emp VALUES('01',20000);
    1 ROW created.
    SQL> INSERT INTO emp VALUES('02',20000);
    1 ROW created.
    SQL> INSERT INTO emp VALUES('03',10000);
    1 ROW created.
    SQL> INSERT INTO emp VALUES('04',1239);
    1 ROW created.
    SQL> INSERT INTO emp VALUES('05',1211);
    1 ROW created.
    SQL> INSERT INTO emp VALUES('06',18900);
    1 ROW created.
    SQL> INSERT INTO emp VALUES('07',26000);
    1 ROW created.
    SQL> INSERT INTO emp VALUES('08',12991);
    1 ROW created.
    SQL> INSERT INTO emp VALUES('09',9812);
    1 ROW created.
    SQL> INSERT INTO emp VALUES('10',6701);
    1 ROW created.
    SQL> INSERT INTO emp VALUES('11',9012);
    1 ROW created.
    SQL> INSERT INTO emp VALUES ('12',8800);
    1 ROW created.
    SQL> INSERT INTO emp VALUES('13',19000);
    1 ROW created.
    SQL>
    SQL> commit;
    Commit complete.
    SQL>
    SQL> --
    SQL> -- Wrong query
    SQL> --
    SQL> -- Gives top four salaries in the table
    SQL> --
    SQL> SELECT empno, salary
    2 FROM
    3 (SELECT empno, salary FROM emp ORDER BY salary DESC)
    4 WHERE rownum <=5;
    EMPNO                                  SALARY
    ------------------------------ ----------
    07                                        26000
    01                                        20000
    02                                        20000
    13                                        19000
    06                                        18900
    SQL>
     
     
    Here is one way to get the correct answer:

    Code (SQL):
     
    SQL> --
    SQL> -- Gives correct result but is long and convoluted
    SQL> --
    SQL> SELECT empno, salary
    2 FROM emp
    3 WHERE salary = (SELECT salary FROM
    4 (SELECT salary, rownum rn FROM
    5 (SELECT DISTINCT salary
    6 FROM emp
    7 ORDER BY salary DESC)
    8 )
    9 WHERE rn = 5)
    10 /
    EMPNO                                  SALARY
    ------------------------------ ----------
    08                                        12991
    SQL>
     
     
    And a better way:

    Code (SQL):
     
    SQL> --
    SQL> -- A better way to get the correct answer
    SQL> --
    SQL> SELECT empno, salary
    2 FROM
    3 (SELECT empno, salary, dense_rank() OVER (ORDER BY salary DESC) rk
    4 FROM emp)
    5 WHERE rk = 5
    6 /
    EMPNO                                  SALARY
    ------------------------------ ----------
    08                                        12991
    SQL>
     
     
     
  11. sandip250382

    sandip250382 Active Member

    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    80
    Re: Help me to solve this query

    Thanks a lot for your great help and cooperation.
     
  12. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Re: Help me to solve this query

    We can get this way using correlated sub query:

    SELECT A.EMPNO, A.SALARY FROM EMP A WHERE &N = (SELECT COUNT(DISTINCT SALARY) FROM EMP B WHERE A.SALARY <= B.SALARY);

    provide any value for N. suppose you want 5th salary , give N = 5.

    assume that there is deptno and you want top Nth salary of each department
    SELECT A.EMPNO, A.SALARY FROM EMP A WHERE &N = (SELECT COUNT(DISTINCT SALARY) FROM EMP B WHERE A.SAL <= B.SAL AND A.DEPTNO = B.DEPTNO);
     
  13. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Re: Help me to solve this query

    Your first query, as written, does not work:

    Code (SQL):
     
    SQL> SELECT A.EMPNO, A.SALARY FROM EMP A WHERE &N = (SELECT COUNT(DISTINCT SALARY) FROM EMP B WHERE A.SAL <= B.SAL);
    Enter VALUE FOR n: 5
    OLD   1: SELECT A.EMPNO, A.SALARY FROM EMP A WHERE &N = (SELECT COUNT(DISTINCT SALARY) FROM EMP B WHERE A.SAL <= B.SAL)
    NEW   1: SELECT A.EMPNO, A.SALARY FROM EMP A WHERE 5 = (SELECT COUNT(DISTINCT SALARY) FROM EMP B WHERE A.SAL <= B.SAL)
    SELECT A.EMPNO, A.SALARY FROM EMP A WHERE 5 = (SELECT COUNT(DISTINCT SALARY) FROM EMP B WHERE A.SAL <= B.SAL)
                                                                                                           *
    ERROR at line 1:
    ORA-00904: "B"."SAL": invalid identifier

     
     
    Modifying the query to reference the correct column names results in success at the expense of two complete table scans on EMP:

    Code (SQL):
     
    SQL> SELECT A.EMPNO, A.SALARY FROM EMP A WHERE &N = (SELECT COUNT(DISTINCT SALAR                                       Y) FROM EMP B WHERE A.SALARY <= B.SALARY);
    Enter VALUE FOR n: 5
    OLD   1: SELECT A.EMPNO, A.SALARY FROM EMP A WHERE &N = (SELECT COUNT(DISTINCT S                                       ALARY) FROM EMP B WHERE A.SALARY <= B.SALARY)
    NEW   1: SELECT A.EMPNO, A.SALARY FROM EMP A WHERE 5 = (SELECT COUNT(DISTINCT SA                                       LARY) FROM EMP B WHERE A.SALARY <= B.SALARY)
    EMPNO                              SALARY
    ------------------------------ ----------
    08                                  12991

    Execution Plan
    ----------------------------------------------------------
    Plan hash VALUE: 2162172199
    ----------------------------------------------------------------------------
    | Id  | Operation           | Name | ROWS  | Bytes | Cost (%CPU)| TIME     |
    ----------------------------------------------------------------------------
    |   0 | SELECT STATEMENT    |      |     1 |    30 |     9  (12)| 00:00:01 |
    |*  1 |  FILTER             |      |       |       |            |          |
    |   2 |   TABLE ACCESS FULL | EMP  |    13 |   390 |     4   (0)| 00:00:01 |
    |   3 |   SORT GROUP BY     |      |     1 |    13 |            |          |
    |*  4 |    TABLE ACCESS FULL| EMP  |     1 |    13 |     4   (0)| 00:00:01 |
    ----------------------------------------------------------------------------
    Predicate Information (IDENTIFIED BY operation id):
    ---------------------------------------------------
       1 - FILTER( (SELECT COUNT(DISTINCT "SALARY") FROM "EMP" "B" WHERE
                  "B"."SALARY">=:B1)=5)
       4 - FILTER("B"."SALARY">=:B1)
    Note
    -----
       - dynamic sampling used FOR this statement

    Statistics
    ----------------------------------------------------------
              9  recursive calls
              0  db block gets
            108  consistent gets
              0  physical reads
              0  redo SIZE
            581  bytes sent via SQL*Net TO client
            492  bytes received via SQL*Net FROM client
              2  SQL*Net roundtrips TO/FROM client
             12  sorts (memory)
              0  sorts (disk)
              1  ROWS processed
    SQL>

     
    It's an interesting solution.
     
  14. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Re: Help me to solve this query

    we should avoid use of correlated sub query in real time as much as possible, why because it costs so much
     
  15. josephsanmiguel

    josephsanmiguel Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Re: Help me to solve this query

    Here's the long way to do it:

    select b.empno,a.salary
    from
    (select b.empno,a.salary
    from
    (select salary from
    (
    select salary
    from emp
    group by salary
    order by salary desc
    )
    where rownum < = :K--input parameter
    order by salary asc) a,emp b
    where 1=1
    and rownum = 1
    and a.salary = b.salary
    )a,emp b
    where a.salary = b.salary
     
  16. ashishb01

    ashishb01 Active Member

    Messages:
    13
    Likes Received:
    1
    Trophy Points:
    110
    Re: Help me to solve this query

    I am an ameture SQL user but you can try this

    Code (SQL):
    SELECT empno,salary FROM emp_table WHERE salary = (
    SELECT salary FROM emp_table WHERE rownum<6 ORDER BY salary DESC -- this will give top 5 salary
    minus
    SELECT salary FROM emp_table WHERE rownum<5 ORDER BY salary DESC -- this minus will remove te top 4 from the list
    )
     
    Remember, if your table is huge, it will give performence proble. but for small tables (less than 100000 records), this should work fine...
     
  17. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Re: Help me to solve this query

    In this particular case your first subquery returns the top 4 salaries as one of those occurs for two different employees. Executing the minus subtracts the top 3 from that leaving you with the fourth highest salary. Correct solutions have been posted which take this salary duplication into account.
     
  18. burcu6032

    burcu6032 Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    Re: Help me to solve this query

    nice, thanks
     
  19. cruelintension

    cruelintension Active Member

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    55
    Re: Help me to solve this query

    wonderful work!
     
  20. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Re: Help me to solve this query

    Thank you.