Max Salary Based on each Department using SQL

Max Salary Based on each Department using SQL

Find second maximum salary of an employee based on each department. Output contains employee name(from employee table), department name (from department table)

Employee Table:

+-----+------+-----------+-------+----------+-------+------+
|empno| ename|designation|manager| hire_date|    sal|deptno|
+-----+------+-----------+-------+----------+-------+------+
| 7369| SMITH|      CLERK|   7902|1980-12-17| 800.00|    20|
| 7499| ALLEN|   SALESMAN|   7698|1981-02-20|1600.00|    30|
| 7521|  WARD|   SALESMAN|   7698|1981-02-22|1250.00|    30|
| 7566| JONES|    MANAGER|   7839|1981-04-02|2975.00|    20|
| 7654|MARTIN|   SALESMAN|   7698|1981-09-28|1250.00|    30|
| 7698| BLAKE|    MANAGER|   7839|1981-05-01|2850.00|    30|
| 7782| CLARK|    MANAGER|   7839|1981-06-09|2450.00|    10|
| 7788| SCOTT|    ANALYST|   7566|1982-12-09|3000.00|    20|
| 7839|  KING|  PRESIDENT|   NULL|1981-11-17|5000.00|    10|
| 7844|TURNER|   SALESMAN|   7698|1981-09-08|1500.00|    30|
| 7876| ADAMS|      CLERK|   7788|1983-01-12|1100.00|    20|
| 7900| JAMES|      CLERK|   7698|1981-12-03| 950.00|    30|
| 7902|  FORD|    ANALYST|   7566|1981-12-03|3000.00|    20|
| 7934|MILLER|      CLERK|   7782|1982-01-23|1300.00|    10|
+-----+------+-----------+-------+----------+-------+------+

 Department Table:

+------+----------+--------+
|deptno|     dname|     loc|
+------+----------+--------+
|    10|ACCOUNTING|NEW YORK|
|    20|  RESEARCH|  DALLAS|
|    30|     SALES| CHICAGO|
|    40|OPERATIONS|  BOSTON|
+------+----------+--------+

select e.ename,d.dname,e.sal from emp e inner join dept d on e.deptno = d.deptno inner join (select ename,sal,deptno,DENSE_RANK() over(partition by deptno order by sal desc)rn from emp)rn1 on e.deptno = rn1.deptno and e.sal=rn1.sal where rn1.rn=2

+-----+----------+----+
|ename|     dname| sal|
+-----+----------+----+
|ALLEN|     SALES|1600|
|JONES|  RESEARCH|2975|
|CLARK|ACCOUNTING|2450|
+-----+----------+----+