Second Highest Salary in SQL Server

Second Highest Salary in SQL Server

Step 1: Create table

USE [testdb]
GO

DROP TABLE [dbo].[tbl_Salary]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tbl_Salary](
	[ID] [bigint] IDENTITY(1,1) NOT NULL,
	[Salary] [decimal](18, 2) NULL
) ON [PRIMARY]

GO
SET IDENTITY_INSERT [dbo].[tbl_Salary] ON 

GO
INSERT [dbo].[tbl_Salary] ([ID], [Salary]) VALUES (1, CAST(10000.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[tbl_Salary] ([ID], [Salary]) VALUES (2, CAST(20000.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[tbl_Salary] ([ID], [Salary]) VALUES (3, CAST(30000.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[tbl_Salary] ([ID], [Salary]) VALUES (4, CAST(15000.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[tbl_Salary] ([ID], [Salary]) VALUES (5, CAST(25000.00 AS Decimal(18, 2)))
GO
SET IDENTITY_INSERT [dbo].[tbl_Salary] OFF
GO
SELECT MAX(salary) FROM tbl_Salary WHERE Salary NOT IN ( SELECT Max(Salary) FROM tbl_Salary)

Output 25000.00

Here is another solution which uses sub query but instead of IN clause it uses < operator

SELECT MAX(Salary) From tbl_Salary WHERE Salary < ( SELECT Max(Salary) FROM tbl_Salary);

Output 25000.00

2020-01-17