How to find nth highest salary in sql ? #sql #interview
How to find nth highest salary in sql ? #sql #interview #Asp.NetHub #.NetHub This is a common SQL Interview Question. There are different ways of finding the nth highest salary. How to find nth highest salary in SQL Server using a Sub-Query How to find nth highest salary in SQL Server using a CTE How to find the 2nd, 3rd or 10th highest salary. CREATE TABLE [dbo].[Employees]( [ID] int primary key identity, [FirstName] [nvarchar](50) NULL, [LastName] [nvarchar](50) NULL, [Salary] [int] NULL, ) INSERT [dbo].[Employees] ([ID], [FirstName], [LastName], [Salary]) VALUES (1, N'John', N'Hoskins', 70000) INSERT [dbo].[Employees] ([ID], [FirstName], [LastName], [Salary]) VALUES (2, N'Steev', N'Hastings', 60000) INSERT [dbo].[Employees] ([ID], [FirstName], [LastName], [Salary]) VALUES (3, N'Jobs', N'Pound', 45000) INSERT [dbo].[Employees] ([ID], [FirstName], [LastName], [Salary]) VALUES (4, N'Alexa', N'Hoskins', 75000) INSERT [dbo].[Employees] ([ID], [FirstName], [LastName], [Salary]) VALUES (5, N'Ann', N'Hastings', 45000) INSERT [dbo].[Employees] ([ID], [FirstName], [LastName], [Salary]) VALUES (6, N'Marry', N'Lambeth', 30000) INSERT [dbo].[Employees] ([ID], [FirstName], [LastName], [Salary]) VALUES (7, N'Charls', N'Vikings', 35000) INSERT [dbo].[Employees] ([ID], [FirstName], [LastName], [Salary]) VALUES (8, N'Philip', N'Stanmore', 80000) To find the highest salary using the Max() function as shown below. Select Max(Salary) from Employees To get the second highest salary use a sub query along with Max() function Select Max(Salary) from Employees where Salary [ (Select Max(Salary) from Employees) To find nth highest salary using Sub-Query SELECT TOP 1 SALARY FROM ( SELECT DISTINCT TOP N SALARY FROM EMPLOYEES ORDER BY SALARY DESC ) RESULT ORDER BY SALARY To find nth highest salary using CTE declare @SLNO int; Set @SLNO=8; With CTE as( select Salary,ROW_NUMBER() over (order by Salary desc)as SLNO from Employees ) select * from CTE where SLNO=@SLNO; #SQLInterview #SQLQuery #SQLTips #SQLSkills #DatabaseInterview #DBQuery #DBSkills #DatabaseTips #SQLServerInterview #MySQLInterview #PostgreSQLInterview #OracleInterview #SQLServerQuery #MySQLQuery #PostgreSQLQuery #OracleQuery #SQLDevelopment #DBDevelopment #SQLCoding #DatabaseCoding add #ASP.NETHUB #.NETHUB #NthHighestSalarySQL #SQLNthHighestSalaryTutorial
