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
SQL
4 years
10 Answers
2169 views
Great Grand Master 0
Answers ( 10 )
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
A simple query
select distinct(salary) from employee order by salary desc limit n-1, offset 1;
error
select distinct(salary) from employee order by salary desc limit 1, offset n-1;
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
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*/
select e.salary from employee e where n-1= (select count(distinct salary) from employee d where d.salary> e.salary))
select e.salary from employee e
where n-1= (select count(distinct salary) from employee d where d.salary> e.salary));
select e.salary from employee e
where n-1= (select count(distinct salary) from employee d where d.salary> e.salary););
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;