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);
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;
1/* Highest salary by Department/Location */
2SELECT e.ename, e.sal, e.deptno, d.loc
3FROM emp e
4JOIN dept d
5ON e.deptno = d.deptno
6WHERE e.sal in
7(
8 select max(sal)
9 from emp
10 group by deptno
11)