SQL Server Interview Question

Nth Highest salary

1. Select Max(Salary) from Employees where Salary < (Select Max(Salary) from Employees)

2. SELECT TOP 1 SALARY
FROM (
      SELECT DISTINCT TOP N SALARY
      FROM EMPLOYEES
      ORDER BY SALARY DESC
      ) RESULT
ORDER BY SALARY


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


NOTE-the following query can be used to get the nth highest salary. The below query will only work if there are no duplicates.
WITH RESULT AS
(
    SELECT SALARY,
           ROW_NUMBER() OVER (ORDER BY SALARY DESC) AS ROWNUMBER
    FROM EMPLOYEES
)
SELECT SALARY
FROM RESULT

WHERE ROWNUMBER = 3



Delete Duplicate rows in table


WITH EmployeesCTE AS
(
   SELECT *, ROW_NUMBER()OVER(PARTITION BY ID ORDER BY ID) AS RowNumber
   FROM Employees
)

DELETE FROM EmployeesCTE WHERE RowNumber > 1




RANK, DENSE_RANK and ROW_NUMBER

Similarities between RANK, DENSE_RANK and ROW_NUMBER functions
Returns an increasing integer value starting at 1 based on the ordering of rows imposed by the ORDER BY clause (if there are no ties)
ORDER BY clause is required
PARTITION BY clause is optional
When the data is partitioned, the integer value is reset to 1 when the partition changes

SELECT Name, Salary, Gender,
ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNumber,
RANK() OVER (ORDER BY Salary DESC) AS [Rank],
DENSE_RANK() OVER (ORDER BY Salary DESC) AS DenseRank
FROM Employees

Difference between RANK, DENSE_RANK and ROW_NUMBER functions
ROW_NUMBER : Returns an increasing unique number for each row starting at 1, even if there are duplicates.
RANK : Returns an increasing unique number for each row starting at 1. When there are duplicates, same rank is assigned to all the duplicate rows, but the next row after the duplicate rows will have the rank it would have been assigned if there had been no duplicates. So RANK function skips rankings if there are duplicates.
DENSE_RANK : Returns an increasing unique number for each row starting at 1. When there are duplicates, same rank is assigned to all the duplicate rows but the DENSE_RANK function will not skip any ranks. This means the next row after the duplicate rows will have the next rank in the sequence