## SQL interview Question | Second Highest Salary – Ranking

Question

Write a SQL query to get second highest query using Ranking

in progress 2
3 years 27 Answers 2109 views Grand Master 0

1. Sorry it a private answer.

2. select * from (
select e.*, rank() over (order by salary desc) as rank from Employee e
) where rank = 2;

3. with base as
( select dense_rank() over ( order by salary desc ) from Table A ) RN
select salary from base where RN = 2 limit 1

4. SELECT name, Salary,
DENSE_RANK() OVER(ORDER BY Salary DESC) AS Rank WHERE Rank=2
FROM table

5. select salary from
(select salary, dense_rank()over(order by salary desc) as DenseRank
from table) as t
where t.DenseRank =2

6. select * from (select *,dense_rank() over (order by salary desc) as salary_rank from table ) t where t.salary_rank=2;

7. select min(salary) from
(select Distinct Top 2 salary from Table
order by salary desc) as Result
Order by salary;

8. SELECT salary
FROM
(SELECT
salary
,DENSE_RANK() OVER (ORDER BY salary DESC) AS rank
FROM table
) AS a
WHERE rank = 2

9. select salary from (select salary, rank() over(order by salary desc) my_rank from table) as t where t.my_rank = 2;

10. select salary from (select salary,rank() over(order by salary desc) as ranking from table) where ranking=2;

11. With Second AS
(SELECT Name, dense_rank() over (ORDER BY Salary Desc) as Rank
FROM Employee)
SELECT Salary FROM Second
WHERE Rank = 2;

12. select salary ,
from ( select * , rank() over( order by salary DESC) rank,
from table_name )
where rank = 2

13. Oracle also provides a RANK function that just assigns a ranking numeric value (with 1 being the highest) for some sorted values. So, we can use this SQL in Oracle to find the nth highest salary using the RANK function:

select * FROM (
select Salary,rank() over (order by Salary DESC) ranking
from table
)
WHERE ranking = N;

The rank function will assign a ranking to each row starting from 1.

14. SQL Server Solution:
Note: Dense_rank() has been used to handle duplicate salaries, if there are any.
With result as
{
select salary,
dense_rank() over (order by salary desc) as salaryrank
from employees
}

select top 1 salary from result where salaryrank = 2

15. SELECT Salary, Name
FROM
(SELECT Salary,
RANK() OVER(ORDER BY Salary DESC) RANK
FROM TABLE)
WHERE RANK = 2;

16. SELECT *
FROM
(SELECT Salary, DENSE_RANK() OVER(ORDER BY Salary DESC) RANK
FROM TABLE)
Where RANK =2

As there is small difference between RANK() and DENSE_RANK()
DENSE_RANK() give same rank to dublicate values whereas RANK() give different rank to dublicate values.

17. SELECT salary, Dense_Rank() OVER (ORDER BY salary DESC) As Rank
FROM emp
WHERE Rank = 2

18. SELECT Salary, Name
FROM
(SELECT Salary,
RANK() OVER(ORDER BY Salary DESC) RANK
FROM TABLE)
WHERE RANK = 2;

19. FROM
(SELECT
salary
,DENSE_RANK() OVER (ORDER BY salary DESC) AS rank
FROM table
) AS a
WHERE rank = 2

20. SELECT salary
FROM
(SELECT
salary
, DENSE_RANK() OVER (ORDER BY salary DESC) AS rank
FROM table)
WHERE rank = 2

21. select salary
from
(
select salary,dense_rank over(order by salary desc) as rankk
order by rankk
) as rank_query
where rankk=2

22. Select * from
(Select *,dense_rank() over (order by salary desc) as rank from Employee) where rank=2;

23. Select * from
(Select e. *,dense_rank() over (order by salary desc) as rank from Employee as e) where rank=2;

24. Select * from
(Select e. *, dense_rank() over (order by salary desc) as rank from Employee e) where rank=2;

25. SELECT EMP_ID, NAME, SALARY
FROM (SELECT SALARY, EMP_ID,NAME, DENSE_RANK() OVER(ORDER BY SALARY DESC) AS RNK
FROM EMPLOYEE)
WHERE RNK=2;