SQL interview Question | Second Highest Salary – Ranking

Question

Write a SQL query to get second highest query using Ranking

in progress 2
TheDataMonk 3 years 27 Answers 1952 views Grand Master 0

Answers ( 27 )

  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

    1

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

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

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

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

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

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

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

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

  12. 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.

  13. 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

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

  15. 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.

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

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

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

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

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

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

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

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

  24. 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;

Leave an answer

Browse
Browse