Write a query to find second highest salary of an employee?

select max(salary) from employees where salary < (select max(salary) from employees)

select * from employee

where salary = (select  max(salary) from employee e 

where salary<(select max(salary) from employee))

select *

  from (select employee_name,

               salary,

               DENSE_RANK() OVER(ORDER BY salary) sal_rank

          from employees)

 where sal_rank = 2;

Select Max(sal) from Employee where sal Not In(Select Max(sal) From Emp)

WITH query_a AS 

( SELECT DENSE_RANK() OVER(ORDER BY sal DESC) sal_rank,

             employee, 

             sal

  FROM employee )

SELECT employee,

            sal

  FROM query_a

WHERE sal_rank = 2;

select distinct sal from employee a where &N = ( select count(distinct(b.sal) from employee b where a.sal <= b.sal) 

Replace N by number which is requied like if its second max then N =2 and so on....

other modification :

 select * from (select  rownum rank, a.* from

    (select salary from employees 

    orderby salary desc)a) where rank=2;

select * from (select salary,row_number() over(order by salary desc) rn from employee) where rn =2;

select emp_id, salary

from(

     select emp_id,salary,row_number() over (order by salary desc) as salary_rank

     from emp

) sub

where salary_rank = 2 ;

having the empno as the key

select tb.* from (select t.*,row_number() over (partition by t.empno order by t.salary desc) salno  from employee t) tb where tb.salno=2