My solution...

Without Rank...

Pretty straightforward...

Using Rank Function....

...

Nice Challenge to refresh the memory :)...

with CTE as(select row_number() over(partition by deptno order by salary desc) row,salary,deptno from empname) select * from CTE where row=2 ...

;with CTE_SAL(EmployeeID, EmployeeName, Department, Salary,ROW_ID) AS(select top 2 EmployeeID, EmployeeName, Department, Salary ,ROW_NUMBER() OVER(ORDER BY SALARY DESC) from employees) select EmployeeID, EmployeeName, Department, Salary from CTE_SAL ...

Just use a CTE to calculate a RANK, partitioned by Department and ordered by Salary. Then select from the CTE where Rank = 2...

dense_rank...