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.
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.