Re: Find employee with third salary!

From: Hernan Cortez <HernanCortez1_at_msn.com>
Date: Tue, 24 Feb 2004 01:22:18 +0100
Message-ID: <c1e5fk$1gr5bd$1@ID-221087.news.uni-berlin.de>

Hello

Daniel, if you are picky, please be it full ... your results are wrong.

CREATE TABLE Employees
( empid int,
  ename varchar(50),
  salary int
 );

insert into Employee values (1, 'emp1', 1000);
insert into Employee values (2, 'emp2', 1001);
insert into Employee values (3, 'emp3', 1002);
insert into Employee values (4, 'emp4', 1003);
insert into Employee values (5, 'emp5', 1000);
insert into Employee values (6, 'emp6', 1001);
insert into Employee values (7, 'emp7', 1003);

Question:
Find the employee with the third largest salary in the organization

The third largest salary is 1001 ...

SQL> select * from EMPLOYEE order by SALARY, EMPID   2 /

     EMPID ENAME                    SALARY


---------- -------------------- ----------

         1 emp1                       1000
         5 emp5                       1000
         2 emp2                       1001   third largest
         6 emp6                       1001   third largest
         3 emp3                       1002   Second largest
         4 emp4                       1003   Largest
         7 emp7                       1003   Largest

The result of

SQL> SELECT *
  2 FROM ( SELECT empid,

  3                  ename,
  4                  salary,
  5                  RANK() OVER (ORDER BY salary DESC) EMPRANK
  6           FROM   employee )

  7 WHERE emprank = 3
  8 /

with 1002 is wrong!

     EMPID ENAME                    SALARY    EMPRANK


---------- -------------------- ---------- ----------

         3 emp3                       1002          3

Solution 1: ORDER BY ascending, because RANK takes the max value first

SQL> SELECT *
  2 FROM ( SELECT empid,

  3                  ename,
  4                  salary,
  5                  RANK() OVER (ORDER BY salary ASC) EMPRANK
  6           FROM   employee )

  7 WHERE emprank = 3
  8 /

     EMPID ENAME                    SALARY    EMPRANK


---------- -------------------- ---------- ----------

         2 emp2                       1001          3
         6 emp6                       1001          3

Solution 2: Without RANK

select *
from EMPLOYEE
where SALARY = ( select max(SALARY)

                  from   EMPLOYEE
                  where  SALARY < ( select max(SALARY)
                                    from   EMPLOYEE
                                    where  SALARY < (select max(SALARY)
                                                     from   EMPLOYEE )
                                  )
                )

/

     EMPID ENAME                    SALARY


---------- -------------------- ----------

         2 emp2                       1001
         6 emp6                       1001

Kind regards Received on Mon Feb 23 2004 - 18:22:18 CST