Sql - Sql Server Interview Questions and answers

Sql Server Interview Questions and Answers

Q1.How to find nth highest salary in SQL

Get 2nd Highest Salary using subquery.

SELECT  MAX(Salary) from Employees Where Salary <(Select MAX(Salary) from Employees)

select DISTINCT TOP 2 Salary from Employees order by Salary Desc

Salary
7000


select  salary from
(
select DISTINCT TOP 2 Salary from Employees order by Salary Desc
)RESULT
Order By Salary

Salary
7000
8000

Select TOP 1 Salary from (
select DISTINCT TOP 2 Salary from Employees order by Salary Desc
)RESULT
Order By Salary

Salary
7000

3rd highest salary:

Select TOP 1 Salary from (
select DISTINCT TOP 3 Salary from Employees order by Salary Desc
)RESULT
Order By Salary


Select Salary, DENSE_RANK( )  OVER (Order By Salary DESC) as DENSERANK
from Employees

Salary DENSERANK
8000   1
7000   2
7000   2
6000   3
5000   4
4000   3


WITH RESULT AS
(

Select Salary, DENSE_RANK( )  OVER (Order By Salary DESC) as DENSERANK
from Employees

)
Select top 1 Salary from Result where Result.DENSERANK=2

--------------------------------------------------------------------------------------------------------------------------

--To find nth highest salary using sub-query

SELECT  TOP 1 SALARY
FROM (

                  SELECT  DISTINCT  TOP N SALARY
                  FROM EMPLOYEES
                  ORDER BY SALARY DESC

                ) RESULT
ORDER BY  SALARY


--To find nth highest salary using CTE

WITH RESULT AS
(

      SELECT SALARY,
                      DENSE_RANK() OVER (ORDER BY SALARY DESC) AS DENSERANK
      FROM EMPLOYEES
)
SELECT TOP 1  SALARY FROM RESULT WHERE DENSERANK = N

-------------------------------------------------------------------------------------------------------------------------


IF IT HAS DUPLICATES THE BELOW WILL NOT WORK.

WITH RESULT AS
(

      SELECT  SALARY,
                       ROW_NUMBER() OVER (ORDER BY SALARY DESC) AS ROWNUMBER
      FROM EMPLOYEES
)
SELECT SALARY FROM RESULT WHERE ROWNUMBER = N

THE ROW_NUMBER() give sequential number.

Salary  ROWNUMBER
8000    1
7000    2
7000    3
6000    4