Total Page Preview:   000000017877

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 DESCAS 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



Comments


                           
                           

                           

                           

Facebook User: