## SQL interview Question | Second Highest Salary – Ranking

Question

Write a SQL query to get second highest query using Ranking

in progress
2

SQL
3 years
27 Answers
2109 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;