cte in sql

CTE are commonly used for storing data temporarily in SQL Server.

delete duplicate records in table using cte :

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



N th highest salary

using Max() function:

Select Max(Salary) from Employees

use a sub query along with Max() function :

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

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


To find 2nd highest salary we can use any of the above queries. Simple replace N with 2.

Similarly, to find 3rd highest salary, simple replace N with 3.

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