SELECT employee_id, emp_name, dept_name, salary,
       NTH_VALUE(salary, 2) OVER (PARTITION BY dept_name
                                      ORDER BY salary DESC
                                  ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
                                 ) rows_value,
       NTH_VALUE(salary, 3) OVER (PARTITION BY dept_name
                                      ORDER BY salary DESC
                                  RANGE BETWEEN 1000 PRECEDING AND 1000 FOLLOWING
                                 ) range_value
 FROM emp_hierarchy
WHERE dept_name IN ('IT', 'Finance')
ORDER BY 3, 4 DESC;