Nth PL/SQL to get Second largest Salary from table

09:00 PL/SQL No Comments

Today, I get an interview in which PL/SQL question was asked, we have a table lets say employee_master which has salary of employee, so requirement is to fetch the employee details who has second largest salary in table, This requirement can be implimented in various ways, Please find below the SQLs to achieve this:

1. select top 1 Salary_amount
   from (select top 2 Salary_amount from employee_master order by salary_amount desc) a
   order by salary_amount asc
2. ;with CTE AS(
   select row_number() over(order by salary_amount Desc) as id, salary_amount
   from employee_master
   )
   select * from CTE where id = 2 
3. DECLARE @SQL VARCHAR(2000), @N INT --@N is level at which you  --required Salary                                         SET @N = 3
   SET @N = @N - 1
    SET @sql = 'select top 1 salary_amount from employee_master where salary not in ( SELECT TOP ' + CAST(@n AS VARCHAR(100))  + ' salary FROM ABC  )'

EXEC (@SQL)

4. SELECT MAX(SALARY) FROM EMPLOYEE WHERE SALARY NOT IN (SELECT MAX(SALARY) FROM EMPLOYEE)

Please do let me know in case of any addition, looking forward to see your comments for ad-ons.