SQL Interview Questions | Nth Highest Salary

Question
NAME SALARY Date of Joining
Angad 50,000 05 July
Bhuvan 52,000 19 March
Danish 77,000 06 September
Manhar 64,000 15 May
Shivraj 50,000 10 July
Tanuj 55,000 13 August

 

How will you get the data of the Nth highest salary from the table? Also, find the third highest salary by substituting values of N.

in progress 1
Dhruv2301 4 years 10 Answers 2365 views Great Grand Master 0

Answers ( 10 )

  1. select * from (
    select Name,salary,dense_rank() OVER(order by salary desc)r from employee)
    where r = n

    Note : To find the 3rd highest salary substitute n =3

  2. A simple query
    select distinct(salary) from employee order by salary desc limit n-1, offset 1;

  3. select name , desne_rank() over (order by salary desc) as rank
    from employees
    where rank= n
    (Here put n= 3 for third highest salary)

    • SELECT A.SALARY
      FROM ( SELECT NAME, SALARY, DENSE_RANK() OVER (ORDER BY SALARY DESC) RANK FROM EMPLOYEE) A
      WHERE A.RANK = N;
      SUBSTITUTE N WITH 3 FOR FINDING THIRD HIGHEST SALARY

  4. Select Min(Salary) from
    (Select Distinct Top N Salary from Employees
    order by Salary Desc) as Result
    Order By Salary

    /* Replace N =3 for Third Highest Salary*/

  5. select e.salary from employee e where n-1= (select count(distinct salary) from employee d where d.salary> e.salary))

  6. select e.salary from employee e
    where n-1= (select count(distinct salary) from employee d where d.salary> e.salary));

  7. select e.salary from employee e
    where n-1= (select count(distinct salary) from employee d where d.salary> e.salary););

  8. THIS WILL GIVE DUPLICATE VALUES TOO
    SELECT *
    FROM (SELECT *, DENSE_RANK( ) OVER (ORDER BY SALARY) AS RN
    FROM TABLENAME)
    WHERE RN=N;

    #IN CASE YOU WANT DON’T WANT DUPLICATE VALUES ONLY USE

    SELECT *
    FROM (SELECT *, RANK( ) OVER (ORDER BY SALARY) AS RN
    FROM TABLENAME)
    WHERE RN=N;

Leave an answer

Browse
Browse