Today we will look into the famous problem of "Finding the Second highest salary of Employees for each department"

Well, this question has already been asked in PLSQL Challenge 2 and henceforth we are not going to discuss about the introduction of the same here

Sample Data

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

Expected Results

EmployeeID  EmployeeName    Department      Salary   
----------- --------------- --------------- ---------
10          S Martin        Back-Office     15000.00
11          J Garcia        Back-Office     15000.00
2           D Michael       Finance         25000.00
3           A Smith         Finance         25000.00
7           J Miller        IT              50000.00
8           L Lewis         IT              50000.00

Sample Script

CREATE TABLE PLC2_Employees (
	EmployeeID Serial ,
	EmployeeName Character Varying(15),
	Department Character Varying(15),
	Salary NUMERIC(16,2)
);


INSERT INTO PLC2_Employees(EmployeeName, Department, Salary) VALUES('T Cook','Finance', 40000);
INSERT INTO PLC2_Employees(EmployeeName, Department, Salary) VALUES('D Michael','Finance', 25000);
INSERT INTO PLC2_Employees(EmployeeName, Department, Salary) VALUES('A Smith','Finance', 25000);
INSERT INTO PLC2_Employees(EmployeeName, Department, Salary) VALUES('D Adams','Finance', 15000);

INSERT INTO PLC2_Employees(EmployeeName, Department, Salary) VALUES('M Williams','IT', 80000);
INSERT INTO PLC2_Employees(EmployeeName, Department, Salary) VALUES('D Jones','IT', 40000);
INSERT INTO PLC2_Employees(EmployeeName, Department, Salary) VALUES('J Miller','IT', 50000);
INSERT INTO PLC2_Employees(EmployeeName, Department, Salary) VALUES('L Lewis','IT', 50000);

INSERT INTO PLC2_Employees(EmployeeName, Department, Salary) VALUES('A Anderson','Back-Office', 25000);
INSERT INTO PLC2_Employees(EmployeeName, Department, Salary) VALUES('S Martin','Back-Office', 15000);
INSERT INTO PLC2_Employees(EmployeeName, Department, Salary) VALUES('J Garcia','Back-Office', 15000);
INSERT INTO PLC2_Employees(EmployeeName, Department, Salary) VALUES('T Clerk','Back-Office', 10000);

Solution 1: Using Rank Analytical Function

With CTE As(
Select 
	 Rank() Over(Partition By Department Order By Salary Desc) "Rn"
	,* 	
From PLC2_Employees)
Select  
	EmployeeID
	,EmployeeName
	,Department
	,Salary
From CTE
Where "Rn" = 2
Order By 
	Department
	,EmployeeID

Solution 2: Using Dense_Rank Analytical Function

With CTE As(
Select 
	 Dense_Rank() Over(Partition By Department Order By Salary Desc) "Rn"
	,* 	
From PLC2_Employees)
Select  
	EmployeeID
	,EmployeeName
	,Department
	,Salary
From CTE
Where "Rn" = 2
Order By 
	Department
	,EmployeeID

Solution 3: Using Co-related subquery

Select	 
	e1.EmployeeID
	,e1.EmployeeName
	,e1.Department
	,e1.Salary
From  
(
	Select 
		MAX(Salary) Salary
		,Department		
	From PLC2_Employees x
	Where (
		Select 
			MAX(Salary) 
		From PLC2_Employees y
		Where x.department = y.department
	     )> Salary 
		
	Group By Department
) e2
Inner Join PLC2_Employees e1
On e1.Department = e2.Department
And e1.Salary = e2.Salary
Order By 
	e1.Department
	, e1.Salary desc
	,e1.EmployeeID

Solution 4: Using Co-related subquery

Select Distinct *
From PLC2_Employees e1
Where 2 = ( Select Count(Distinct e2.Salary)
            From PLC2_Employees e2
            Where e2.Department = e1.Department
            And e2.Salary >= e1.Salary 
          )
Order By 
	e1.Department
	, e1.Salary desc
	,e1.EmployeeID

Hope this helps