How to Find Max Salary, Second Highest And Nth Max Salary In SQL

Introduction

In this article we will learn How to Get Nth Max salary and also how to get Max salary and get second highest salary from a table.

Previous Updates

In previous articles we have learnt What is CTE in SQL and use , Stuff and Replace in SQl. Temp table and table variable .Group By in SQL Server and its use. If Else and Case statement  , While Loop . What is Cursor in sql and use of cursor. Difference between Row_Number(), Rank(), Rank_Density() with example.

SQL Table Structure

CREATE TABLE [dbo].[Employee] (     [ID]      INT           NOT NULL,     [Name]    VARCHAR (50)  NULL,     [Salary] DECIMAL (18,2) NOT NULL );

INSERT INTO Employee VALUES( 1, 'KP', 10000) INSERT INTO Employee VALUES( 2, 'Nicks', 50000) INSERT INTO Employee VALUES( 3, 'Mark', 4000) INSERT INTO Employee VALUES( 4, 'Lunous', 7000) INSERT INTO Employee VALUES( 5, 'Jennifer', 9000)

Find Max/Highest Salary Of An Employee

Select MAX(Salary) from Employee

Find Second Highest Salary 

Select MAX(Salary) from Employee  Where Salary Not In (Select MAX(Salary) from Employee)

OR

SELECT  MAX(Salary) from Employee WHERE Salary <> (select MAX(Salary) from Employee )

Find Nth Highest Salary 

We already learnt how to use CTE and CTE examples in Previous update. here we use the same CTE to get the Nth max salary from a table using Row_Number() function.

WITH CTE AS (     SELECT Salary,            RN = ROW_NUMBER() OVER (ORDER BY Salary DESC)     FROM Employee ) SELECT Salary FROM CTE WHERE RN =4

All above query Result look like