Category >
MSSQLSERVER
|| Published on :
Wednesday, February 10, 2016 || Views:
9660
||
find third highest salary in sql server sql server find third highest salary
Introduction
Here Pawan Kumar will explain how to find third highest salary in sql server.
Description
In previous post I have explained
jQuery to Dynamically Change or Set Placeholder Text in Asp.Net TextBox,
jQuery to Validate File Type and Size before Uploading through Asp.Net FileUpload Control,
How to clear the file upload control value using jQuery / JavaScript,
Export GridView selected rows to Excel or word in ASP.NET using CSharp,
How to allow numbers, backspace, delete, left and right arrow and Tab Keys to the TextBox using Javascript or JQuery in ASP.NET,
Upload And Read Excel File into DataSet in Asp.Net using C#, and many more articles.
Now I will explain How to How to find third highest salary in sql server.
So follow the steps to learn How to find third highest salary in sql server.
Step 1: Create a new database with name "Test" in MS-SQL Server.
Step 2: Create a table in MS-SQL Server using the below script.
USE [test]
GO
/****** Object: Table [dbo].[tblEmployees] Script Date: 02/10/2016 14:39:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblEmployees](
[ID] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL,
[Gender] [nvarchar](20) NULL,
[JobTitle] [nvarchar](20) NULL,
[WebSite] [nvarchar](100) NULL,
[Salary] [int] NULL,
[HireDate] [datetime] NULL,
PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Step 3: Add some dummy data to table create in step 2. Script for adding data.
USE [test]
GO
/****** Object: Table [dbo].[tblEmployees] Script Date: 02/10/2016 14:45:46 ******/
SET IDENTITY_INSERT [dbo].[tblEmployees] ON
INSERT [dbo].[tblEmployees] ([ID], [FirstName], [LastName], [Gender], [JobTitle], [WebSite], [Salary], [HireDate]) VALUES (1, N'Mark', N'Hastings', N'Male', N'Developer', N'http://pragimtech.com', 50000, CAST(0x00006F6700000000 AS DateTime))
INSERT [dbo].[tblEmployees] ([ID], [FirstName], [LastName], [Gender], [JobTitle], [WebSite], [Salary], [HireDate]) VALUES (2, N'Maria', N'Nicholas', N'Female', N'Developer', N'http://csharp-video-tutorials.blogspot.com', 50000, CAST(0x00006DD200000000 AS DateTime))
INSERT [dbo].[tblEmployees] ([ID], [FirstName], [LastName], [Gender], [JobTitle], [WebSite], [Salary], [HireDate]) VALUES (3, N'Robert', N'Stephenson', N'Male', N'Sr. Developer', NULL, 45000, CAST(0x0000727700000000 AS DateTime))
INSERT [dbo].[tblEmployees] ([ID], [FirstName], [LastName], [Gender], [JobTitle], [WebSite], [Salary], [HireDate]) VALUES (4, N'Mary', N'Quant', N'Female', N'Sr. Developer', NULL, 65000, CAST(0x0000714800000000 AS DateTime))
INSERT [dbo].[tblEmployees] ([ID], [FirstName], [LastName], [Gender], [JobTitle], [WebSite], [Salary], [HireDate]) VALUES (5, N'John', N'Stenson', N'Male', N'Sr. Developer', N'http://csharp-sample-programs.blogspot.com', 55000, CAST(0x0000724800000000 AS DateTime))
INSERT [dbo].[tblEmployees] ([ID], [FirstName], [LastName], [Gender], [JobTitle], [WebSite], [Salary], [HireDate]) VALUES (6, N'Gilbert', N'Sullivan', N'Male', N'Developer', NULL, 56000, CAST(0x00006F6700000000 AS DateTime))
INSERT [dbo].[tblEmployees] ([ID], [FirstName], [LastName], [Gender], [JobTitle], [WebSite], [Salary], [HireDate]) VALUES (7, N'Rob', N'Gerald', N'Male', N'Sr. Developer', N'http://www.venkatcsharpinterview.blogspot.com', 45000, CAST(0x00006C1000000000 AS DateTime))
INSERT [dbo].[tblEmployees] ([ID], [FirstName], [LastName], [Gender], [JobTitle], [WebSite], [Salary], [HireDate]) VALUES (8, N'Ron', N'Simpson', N'Male', N'Developer', N'http://www.venkataspinterview.blogspot.com', 75000, CAST(0x0000678600000000 AS DateTime))
INSERT [dbo].[tblEmployees] ([ID], [FirstName], [LastName], [Gender], [JobTitle], [WebSite], [Salary], [HireDate]) VALUES (9, N'Sara', N'Solomon', N'Female', N'Sr. Developer', NULL, 45000, CAST(0x00006F6700000000 AS DateTime))
INSERT [dbo].[tblEmployees] ([ID], [FirstName], [LastName], [Gender], [JobTitle], [WebSite], [Salary], [HireDate]) VALUES (10, N'Rad', N'Wicht', N'Male', N'Sr. Developer', N'http://wcfinterviewquestions.blogspot.com', 55000, CAST(0x0000730B00000000 AS DateTime))
INSERT [dbo].[tblEmployees] ([ID], [FirstName], [LastName], [Gender], [JobTitle], [WebSite], [Salary], [HireDate]) VALUES (11, N'Julian', N'John', N'Male', N'Developer', N'http://venkatsqlinterview.blogspot.com', 65000, CAST(0x0000742000000000 AS DateTime))
INSERT [dbo].[tblEmployees] ([ID], [FirstName], [LastName], [Gender], [JobTitle], [WebSite], [Salary], [HireDate]) VALUES (12, N'James', N'Bynes', N'Male', N'Sr. Developer', N'http://mvcquestions.blogspot.com', 55000, CAST(0x0000711D00000000 AS DateTime))
INSERT [dbo].[tblEmployees] ([ID], [FirstName], [LastName], [Gender], [JobTitle], [WebSite], [Salary], [HireDate]) VALUES (13, N'Mary', N'Ward', N'Female', N'Developer', N'http://hrinterviewquestions.blogspot.com', 60000, CAST(0x0000722100000000 AS DateTime))
INSERT [dbo].[tblEmployees] ([ID], [FirstName], [LastName], [Gender], [JobTitle], [WebSite], [Salary], [HireDate]) VALUES (14, N'Michael', N'Niron', N'Male', N'Sr. Developer', NULL, 45000, CAST(0x00006BF000000000 AS DateTime))
INSERT [dbo].[tblEmployees] ([ID], [FirstName], [LastName], [Gender], [JobTitle], [WebSite], [Salary], [HireDate]) VALUES (15, N'Mark', N'Hastings', N'Male', N'Developer', N'http://pragimtech.com', 50000, CAST(0x00006F6700000000 AS DateTime))
INSERT [dbo].[tblEmployees] ([ID], [FirstName], [LastName], [Gender], [JobTitle], [WebSite], [Salary], [HireDate]) VALUES (16, N'Maria', N'Nicholas', N'Female', N'Developer', N'http://csharp-video-tutorials.blogspot.com', 50000, CAST(0x00006DD200000000 AS DateTime))
INSERT [dbo].[tblEmployees] ([ID], [FirstName], [LastName], [Gender], [JobTitle], [WebSite], [Salary], [HireDate]) VALUES (17, N'Robert', N'Stephenson', N'Male', N'Sr. Developer', NULL, 45000, CAST(0x0000727700000000 AS DateTime))
INSERT [dbo].[tblEmployees] ([ID], [FirstName], [LastName], [Gender], [JobTitle], [WebSite], [Salary], [HireDate]) VALUES (18, N'Mary', N'Quant', N'Female', N'Sr. Developer', NULL, 65000, CAST(0x0000714800000000 AS DateTime))
INSERT [dbo].[tblEmployees] ([ID], [FirstName], [LastName], [Gender], [JobTitle], [WebSite], [Salary], [HireDate]) VALUES (19, N'John', N'Stenson', N'Male', N'Sr. Developer', N'http://csharp-sample-programs.blogspot.com', 55000, CAST(0x0000724800000000 AS DateTime))
INSERT [dbo].[tblEmployees] ([ID], [FirstName], [LastName], [Gender], [JobTitle], [WebSite], [Salary], [HireDate]) VALUES (20, N'Gilbert', N'Sullivan', N'Male', N'Developer', NULL, 56000, CAST(0x00006F6700000000 AS DateTime))
INSERT [dbo].[tblEmployees] ([ID], [FirstName], [LastName], [Gender], [JobTitle], [WebSite], [Salary], [HireDate]) VALUES (21, N'Rob', N'Gerald', N'Male', N'Sr. Developer', N'http://www.venkatcsharpinterview.blogspot.com', 45000, CAST(0x00006C1000000000 AS DateTime))
INSERT [dbo].[tblEmployees] ([ID], [FirstName], [LastName], [Gender], [JobTitle], [WebSite], [Salary], [HireDate]) VALUES (22, N'Ron', N'Simpson', N'Male', N'Developer', N'http://www.venkataspinterview.blogspot.com', 75000, CAST(0x0000678600000000 AS DateTime))
INSERT [dbo].[tblEmployees] ([ID], [FirstName], [LastName], [Gender], [JobTitle], [WebSite], [Salary], [HireDate]) VALUES (23, N'Sara', N'Solomon', N'Female', N'Sr. Developer', NULL, 45000, CAST(0x00006F6700000000 AS DateTime))
INSERT [dbo].[tblEmployees] ([ID], [FirstName], [LastName], [Gender], [JobTitle], [WebSite], [Salary], [HireDate]) VALUES (24, N'Rad', N'Wicht', N'Male', N'Sr. Developer', N'http://wcfinterviewquestions.blogspot.com', 55000, CAST(0x0000730B00000000 AS DateTime))
INSERT [dbo].[tblEmployees] ([ID], [FirstName], [LastName], [Gender], [JobTitle], [WebSite], [Salary], [HireDate]) VALUES (25, N'Julian', N'John', N'Male', N'Developer', N'http://venkatsqlinterview.blogspot.com', 65000, CAST(0x0000742000000000 AS DateTime))
INSERT [dbo].[tblEmployees] ([ID], [FirstName], [LastName], [Gender], [JobTitle], [WebSite], [Salary], [HireDate]) VALUES (26, N'James', N'Bynes', N'Male', N'Sr. Developer', N'http://mvcquestions.blogspot.com', 55000, CAST(0x0000711D00000000 AS DateTime))
INSERT [dbo].[tblEmployees] ([ID], [FirstName], [LastName], [Gender], [JobTitle], [WebSite], [Salary], [HireDate]) VALUES (27, N'Mary', N'Ward', N'Female', N'Developer', N'http://hrinterviewquestions.blogspot.com', 60000, CAST(0x0000722100000000 AS DateTime))
INSERT [dbo].[tblEmployees] ([ID], [FirstName], [LastName], [Gender], [JobTitle], [WebSite], [Salary], [HireDate]) VALUES (28, N'Michael', N'Niron', N'Male', N'Sr. Developer', NULL, 45000, CAST(0x00006BF000000000 AS DateTime))
INSERT [dbo].[tblEmployees] ([ID], [FirstName], [LastName], [Gender], [JobTitle], [WebSite], [Salary], [HireDate]) VALUES (29, N'Mark', N'Hastings', N'Male', N'Developer', N'http://pragimtech.com', 50000, CAST(0x00006F6700000000 AS DateTime))
INSERT [dbo].[tblEmployees] ([ID], [FirstName], [LastName], [Gender], [JobTitle], [WebSite], [Salary], [HireDate]) VALUES (30, N'Maria', N'Nicholas', N'Female', N'Developer', N'http://csharp-video-tutorials.blogspot.com', 50000, CAST(0x00006DD200000000 AS DateTime))
INSERT [dbo].[tblEmployees] ([ID], [FirstName], [LastName], [Gender], [JobTitle], [WebSite], [Salary], [HireDate]) VALUES (31, N'Robert', N'Stephenson', N'Male', N'Sr. Developer', NULL, 45000, CAST(0x0000727700000000 AS DateTime))
INSERT [dbo].[tblEmployees] ([ID], [FirstName], [LastName], [Gender], [JobTitle], [WebSite], [Salary], [HireDate]) VALUES (32, N'Mary', N'Quant', N'Female', N'Sr. Developer', NULL, 65000, CAST(0x0000714800000000 AS DateTime))
INSERT [dbo].[tblEmployees] ([ID], [FirstName], [LastName], [Gender], [JobTitle], [WebSite], [Salary], [HireDate]) VALUES (33, N'John', N'Stenson', N'Male', N'Sr. Developer', N'http://csharp-sample-programs.blogspot.com', 55000, CAST(0x0000724800000000 AS DateTime))
INSERT [dbo].[tblEmployees] ([ID], [FirstName], [LastName], [Gender], [JobTitle], [WebSite], [Salary], [HireDate]) VALUES (34, N'Gilbert', N'Sullivan', N'Male', N'Developer', NULL, 56000, CAST(0x00006F6700000000 AS DateTime))
INSERT [dbo].[tblEmployees] ([ID], [FirstName], [LastName], [Gender], [JobTitle], [WebSite], [Salary], [HireDate]) VALUES (35, N'Rob', N'Gerald', N'Male', N'Sr. Developer', N'http://www.venkatcsharpinterview.blogspot.com', 45000, CAST(0x00006C1000000000 AS DateTime))
INSERT [dbo].[tblEmployees] ([ID], [FirstName], [LastName], [Gender], [JobTitle], [WebSite], [Salary], [HireDate]) VALUES (36, N'Ron', N'Simpson', N'Male', N'Developer', N'http://www.venkataspinterview.blogspot.com', 75000, CAST(0x0000678600000000 AS DateTime))
INSERT [dbo].[tblEmployees] ([ID], [FirstName], [LastName], [Gender], [JobTitle], [WebSite], [Salary], [HireDate]) VALUES (37, N'Sara', N'Solomon', N'Female', N'Sr. Developer', NULL, 45000, CAST(0x00006F6700000000 AS DateTime))
Step 4: Write the below script to find third highest salary in sql server
SELECT MAX(SALARY) FROM TBLEMPLOYEES
WHERE SALARY NOT IN(SELECT DISTINCT TOP 2 SALARY FROM TBLEMPLOYEES ORDER BY SALARY DESC)
Output:-
Conclusion:
So, In this tutorial we have learned, How to find third highest salary in sql server.