Hierarchical Query Example

Hierarchical query:
This query lists the employee_details with their manager in hierarchical manner from employees table.

SELECT a.employee_id, a.first_name, a.last_name, a.manager_id,
b.first_name mgr_first_name, b.last_name mgr_last_name
FROM
(
select employee_id, first_name, last_name, manager_id
from employees
start with employee_id = 100
connect by prior employee_id = manager_id
) a,
employees b
WHERE a.manager_id = b.employee_id(+)


Regards,
Khwaja

Top-n and nth Least query

Top-n query: Query to find the nth highest salary and the
associated employees from employees table

select a.last_name,a.salary
from employees a
where n = (select count(distinct(b.salary))
from employees b
where a.salary <= b.salary)

nth least query: Query to find the nth least or say 2nd least salary and
the associated employees from employees table

select a.last_name,a.salary
from employees a
where n = (select count(distinct(b.salary))
from employees b
where a.salary >= b.salary)