The challenge is to implement similarly functionality in TSQL, that works similar to TRANSLATE function of ORACLE. ...

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...