SQL : Get nth highest and lowest salary from table

hi in this post i show how to get nth highest or lowest salary from a table.

Below is my table script which i would be using to get the nth highest salary.


SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Employee](
[EmpDeptID] [int] identity ,
[EMpName] [varchar](100) NULL,
[salary] [numeric](18, 0) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[Employee] ([EMpName], [salary]) VALUES ( N'Peter', CAST(5000 AS Numeric(18, 0)))
GO
INSERT [dbo].[Employee] ( [EMpName], [salary]) VALUES (N'Mary', CAST(5000 AS Numeric(18, 0)))
GO
INSERT [dbo].[Employee] ( [EMpName], [salary]) VALUES ( N'Anthony', CAST(500 AS Numeric(18, 0)))
GO
INSERT [dbo].[Employee] ( [EMpName], [salary]) VALUES ( N'Jacob', CAST(1000 AS Numeric(18, 0)))
GO
INSERT [dbo].[Employee] ( [EMpName], [salary]) VALUES ( N'Maria', CAST(400 AS Numeric(18, 0)))
GO

Query to get the 2nd highest salary :

1.

;WITH cte
AS (
 SELECT DENSE_RANK() OVER (
   ORDER BY salary DESC
   ) AS Rank
  ,*
 FROM employee
 )
SELECT *
FROM cte
WHERE rank = 2

 Suppose now you want to get the 3rd highest salary so just replace 2 with 3 in the outer query.
Now suppose you want 1st lowest salary then replace 2 with 4 as 4th has the highest dense rank in all of the records.



2.

This query can be used in lower sql server versions like sql server 2000 where features like CTE is not supported. Hence we can use this below query to get the desired result. The inner query is know as correlated sub query.
 Below is the query to get the 2nd highest salary. Suppose if you want to get 3rd highest salary then replace (1) with (2).
Same can be used to get the lowest salary. Suppose if we want 1st lowest salary then use (3) 

SELECT *
FROM Employee E1
WHERE (1) = (
  SELECT COUNT(DISTINCT (E2.Salary))
  FROM Employee E2
  WHERE E2.Salary > E1.Salary
  )