second max salary in sql

1Both options you find max as a subset and then exclude from main select
2sql> SELECT MAX( col ) FROM table
3 	WHERE col < ( SELECT MAX( col ) FROM table);
4sql> SELECT MAX(col) FROM table 
5WHERE col NOT IN (SELECT MAX(col) FROM table);

1SELECT MAX(salary), MIN(salary) FROM employees;

1SELECT MAX(SALARY) 'SECOND_MAX' FROM EMPLOYEES
2WHERE SALARY <> (SELECT MAX(SALARY) FROM EMPLOYEES);

1/* sql 2nd highest salary employee */
2select sal, ename
3from emp
4where sal =
5    (
6        select max(sal) from emp where sal <
7            (select max(sal) from emp)
8    )
9----------------------------------------------- option 2
10select *
11from 
12(
13    select ename, sal, dense_rank() over(order by sal desc) rank
14    from emp
15)
16where rank =2;

1SELECT MAX(SALARY) 'SECOND_MAX' FROM EMPLOYEES
2WHERE SALARY <> (SELECT MAX(SALARY) FROM EMPLOYEES);
3
4
5OR
6
7Here is the solution for nth highest
8salary from employees table 
9
10SELECT FIRST_NAME , SALARY FROM 
11(SELECT FIRST_NAME, SALARY, DENSE_RANK() OVER
12(ORDER BY SALARY DESC) AS SALARY_RANK
13FROM EMPLOYEES)
14WHERE SALARY_RANK = n; 

1SELECT firstname, MAX(salary)
2FROM department d LEFT OUTER JOIN employee e
3ON (d.department_id = e.department_id)
4GROUP BY department_id;