Find the second highest salary

Question

Well it is a well known question. Consider the below

EmployeeID  EmployeeName    Department      Salary   
----------- --------------- --------------- ---------
1           T Cook          Finance         40000.00
2           D Michael       Finance         25000.00
3           A Smith         Finance         25000.00
4           D Adams         Finance         15000.00
5           M Williams      IT              80000.00
6           D Jones         IT              40000.00
7           J Miller        IT              50000.00
8           L Lewis         IT              50000.00
9           A Anderson      Back-Office     25000.00
10          S Martin        Back-Office     15000.00
11          J Garcia        Back-Office     15000.00
12          T Clerk         Back-Office     10000.00

We need to find out the second highest salary

With Cte As
(
  Select 
    level
    ,Department
    ,Max(Salary) 
 From plc2_employees
 Where level = 2
 Connect By Prior (Salary) > Salary)
Group By level,Department
)

Select 
    Employeeid
    ,EmployeeName
    ,Department
    ,Salary
From plc2_employees e1
Inner Join Cte e2 On e1.Department = e2.Department
Order By 
    e1.Department
    , e1.Salary desc
    ,e1.EmployeeID

is somehow not working... I am not getting the correct result. Could anyone please help me out.

Answer 1

Something like

select * from
(
select EmployeeID, EmployeeName, Department, Salary, 
rank () over (partition by Department order by Salary desc) r
from PLC2_Employees
)
where r = 2

Edit - tested it and it gives the answer you expected.

Answer 2

If you're going to teach yourself how to deal with CONNECT BY, you should first find a problem that is suited to the construct. CONNECT BY is meant for processing data that's in a hierarchical form, which your example is not. Salaries are not related to each other in a hierarchical fashion. Trying to force-fit a construct on the wrong problem is frustrating and doesn't really teach you anything.

Take a look at the classic employee-manager relationship in the demo HR schema you can install with Oracle. All employees report to a manager, including managers (except the top guy). You can then use this schema to create a query to show, for example, the Organization Chart for the company.

Answer 3

START WITH … CONNECT BY is designed to explore data that forms a graph, by exploring all possible descending paths. You specify the root nodes in the START WITH clause and the node connections in the CONNECT BY clause (not in the WHERE clause).

The WHERE clause filters will be processed after the hierachical conditions, same for GROUP BY and HAVING (of course because GROUP BY is computed after WHERE).

Therefore you MUST here CONNECT BY PRIOR department = department for example. You must also avoid that a node connection is done between two salaries when there is an intermediate salary.

Therefore the final query would resemble this:

SELECT level
     , Department
     , Salary
  FROM plc2_employees pe1
 START WITH pe1.salary = (select max(salary) from plc2_employees pe2 WHERE pe2.Department = pe1.Department)
 CONNECT BY PRIOR pe1.Department = pe1.Department
        AND PRIOR pe1.Salary > pe1.Salary
        AND PRIOR pe1.Salary = ( SELECT MIN(Salary) FROM plc2_employees pe3
                                  WHERE pe3.Department = pe1.Department
                                    AND pe3.Salary > pe1.Salary
                               )

The recursion condition states that there is no intermediate salary between the child row and the parent row.

Note that this will really be unefficient…

Answer 4

Try this, it gives second highest salary

select MAX(Salary) as Salary 
from Employee_salary 
where Salary not in (select MAX(Salary) from Employee_salary) 

Answer 5

You can use this query:

select * from 
employee e1 
where 2 = (select count (distinct (salary)) 
from employee e2 
where e2.salary >=e1.salary);

Answer 6

Try this, It gives second highest salary...

select MAX(Salary) as Salary 
from Employee_salary 
where Salary not in (select MAX(Salary) from Employee_salary )

If you want to find nth highest salary than you can use following query.... you need to do just one change..... Put the value of N=nth highest

Cheers....:)

SELECT * FROM Employee_salary Emp1 
WHERE (N-1) = (SELECT COUNT(DISTINCT(Emp2.Salary)) 
               FROM Employee_salary Emp2 
               WHERE Emp2.Salary > Emp1.Salary)

Answer 7

find out second highest salary from employee table having column as salary: Database : DB2

with t as
(
select distinct salary from employee order by salary desc
),
tr as 
(
select salary, row_Number() over() r from t 
)
select salary from tr where r = 2

Answer 8

This will work -

SELECT MIN(Salary) 
FROM employee 
WHERE salary IN (SELECT TOP 2 salary FROM employee ORDER BY salary DESC)

Answer 9

First, select the distinct salaries in descending order (from greatest to least), from that set select the top 2 and put in ascending order (placing number 2 on top), then from those 2 select top 1:

select top 1 s.Salary
  from 
   (select top 2 t.Salary
      from
          (select distinct Salary
             from PLC2_Employees
            order by Salary desc) t
     order by Salary asc) s