Total Page Preview: 000000019804
SQL SERVER - Find Nth Highest Salary or 3rd Highest Salary of Employee
This question is quite a popular question and it is interesting that I have been receiving this question every other day. I have already answer this question here. “How to find Nth Highest Salary of Employee”.
--Get 3rd maximum salary using sub-query:
SELECT TOP 1 [Eid] ,[Salary] FROM
(SELECT TOP 3 [Eid] ,[Salary] FROM [TestDB].[dbo].[Employee] ORDER BY Salary DESC) AS Emp
ORDER BY Salary ASC
--Get 3rd maximum salary using CTE:
Using CTE above query can be re-written as follows:
;WITH Emp([Eid], [Salary]) --Column names for CTE, which are optional
AS
(
SELECT TOP 3 [Eid], [Salary] FROM [TestDB].[dbo].[Employee] ORDER BY Salary DESC
)
--User CTE
SELECT TOP 1 [Eid], [Salary] FROM emp ORDER BY Salary ASC
SQL Script for employee:
CREATE TABLE [dbo].[Employee]
(
[Eid] [float] NULL,
[Ename] [nvarchar](255) NULL,
[Salary] [float] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[Employee] ([Eid], [Ename], [Salary]) VALUES (1, N'Neeraj', 45000)
INSERT [dbo].[Employee] ([Eid], [Ename], [Salary]) VALUES (2, N'Ankit', 5000)
INSERT [dbo].[Employee] ([Eid], [Ename], [Salary]) VALUES (3, N'Akshay', 6000)
INSERT [dbo].[Employee] ([Eid], [Ename], [Salary]) VALUES (4, N'Ramesh', 7600)
INSERT [dbo].[Employee] ([Eid], [Ename], [Salary]) VALUES (5, N'Vikas', 4000)
INSERT [dbo].[Employee] ([Eid], [Ename], [Salary]) VALUES (7, N'Neha', 8500)
INSERT [dbo].[Employee] ([Eid], [Ename], [Salary]) VALUES (8, N'Shivika', 4500)
INSERT [dbo].[Employee] ([Eid], [Ename], [Salary]) VALUES (9, N'Tarun', 9500)
Thanks!
Thank You
About Author
Brijesh Kumar
Database Developer
I have more then 6 years Experience in Microsoft Technologies - SQL Server Database, ETL
Azure Cloud - Azure SQL Database, CosmosDB, Azure Data Factory, PowerBI, Web Job, Azure Function, Azure Storage, Web Apps, Powershall
and Database Migration On-Premise to Azure Cloud.
LinkedIn : https://www.linkedin.com