SQL interview Question | Second Highest Salary – Ranking
Question
Write a SQL query to get second highest query using Ranking
in progress
2
SQL
4 years
27 Answers
2521 views
Grand Master 0
Answers ( 27 )
Sorry it a private answer.
Rank or dense_rank?
select * from (
select e.*, rank() over (order by salary desc) as rank from Employee e
) where rank = 2;
Rank or dense_rank?
with base as
( select dense_rank() over ( order by salary desc ) from Table A ) RN
select salary from base where RN = 2 limit 1
SELECT name, Salary,
DENSE_RANK() OVER(ORDER BY Salary DESC) AS Rank WHERE Rank=2
FROM table
select salary from
(select salary, dense_rank()over(order by salary desc) as DenseRank
from table) as t
where t.DenseRank =2
select * from (select *,dense_rank() over (order by salary desc) as salary_rank from table ) t where t.salary_rank=2;
select min(salary) from
(select Distinct Top 2 salary from Table
order by salary desc) as Result
Order by salary;
SELECT salary
FROM
(SELECT
salary
,DENSE_RANK() OVER (ORDER BY salary DESC) AS rank
FROM table
) AS a
WHERE rank = 2
select salary from (select salary, rank() over(order by salary desc) my_rank from table) as t where t.my_rank = 2;
select salary from (select salary,rank() over(order by salary desc) as ranking from table) where ranking=2;
With Second AS
(SELECT Name, dense_rank() over (ORDER BY Salary Desc) as Rank
FROM Employee)
SELECT Salary FROM Second
WHERE Rank = 2;
select salary ,
from ( select * , rank() over( order by salary DESC) rank,
from table_name )
where rank = 2
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.
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
SELECT Salary, Name
FROM
(SELECT Salary,
RANK() OVER(ORDER BY Salary DESC) RANK
FROM TABLE)
WHERE RANK = 2;
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.
SELECT salary, Dense_Rank() OVER (ORDER BY salary DESC) As Rank
FROM emp
WHERE Rank = 2
SELECT Salary, Name
FROM
(SELECT Salary,
RANK() OVER(ORDER BY Salary DESC) RANK
FROM TABLE)
WHERE RANK = 2;
FROM
(SELECT
salary
,DENSE_RANK() OVER (ORDER BY salary DESC) AS rank
FROM table
) AS a
WHERE rank = 2
SELECT salary
FROM
(SELECT
salary
, DENSE_RANK() OVER (ORDER BY salary DESC) AS rank
FROM table)
WHERE rank = 2
select salary
from
(
select salary,dense_rank over(order by salary desc) as rankk
order by rankk
) as rank_query
where rankk=2
Select * from
(Select *,dense_rank() over (order by salary desc) as rank from Employee) where rank=2;
Select * from
(Select e. *,dense_rank() over (order by salary desc) as rank from Employee as e) where rank=2;
Select * from
(Select e. *, dense_rank() over (order by salary desc) as rank from Employee e) where rank=2;
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;