Find The 3rd Or Nth Highest Salary In A Table Via SubQuery

To find the Nth highest salary, we need to create a table in the database containing some data and to do this use the following procedure.

Step 1

Create a schema of a table named "Employee" in your database as in the following,

create table Employee
(
   ID int identity,
   Name varchar(20),
   Salary float,
   Department varchar(20)
)

Note

I am using SQL Server 2008 in this demo.

Step 2

Insert some values in the table according to the column behaviour like Name and Data Type.

Insert into Employee(Name,Salary,Department)values('A',20000,'Finance')
Insert into Employee(Name,Salary,Department)values('B',10000,'Finance')
Insert into Employee(Name,Salary,Department)values('C',28000,'IT')
Insert into Employee(Name,Salary,Department)values('D',15000,'Finance')
Insert into Employee(Name,Salary,Department)values('E',39000,'Finance')
Insert into Employee(Name,Salary,Department)values('F',12000,'Finance')
Insert into Employee(Name,Salary,Department)values('G',40000,'IT')
Insert into Employee(Name,Salary,Department)values('H',32000,'Finance')
Insert into Employee(Name,Salary,Department)values('I',56000,'IT')
Insert into Employee(Name,Salary,Department)values('J',29000,'Finance')

Step 3

Use the following command to see the data from the table.

select * from Employee

Question How can I get the Nth highest salary like 3rd highest or 4th highest?

Answer

It can be done in many ways but I will demonstrate the easiest way that is very famous and compact. Before getting the Nth Highest salary, we will get the salarys of employees in decreasing order.

SELECT  Name,salary  FROM employee ORDER BY salary desc

Note

I am assuming that we want to find the 3rd highest salary.

Explanation

In the preceding query my motive is that first I will get the highest 3 salaries and then get the minimum salary from those 3 salaries.

There are the following 2 parts of the preceding query,

1. Inner Query - Get the highest 3 salaries
2. Outer Query - Get the minimum salary from those 3 salaries

In the Inner Query I have used the "DISTINCT", "TOP", "ORDER BY" and "DESC" keywords, they mean,

• DISTINCT - for removing the duplicity. 
• TOP - to get the number of upper rows from the set of records like here 3 is used as number.
• ORDER BY - to ordering the set of rows.
• DESC - used with "ORDER BY" to get the data in decreasing order.

So by the inner query we will get output like this,



Now to find the minimum salary from the inner query. To do that I will write the outer query using the "MIN" keyword and aliase the set by "as a" to get the final output like this,

SELECT MIN( salary)
FROM (
-- INNER Query
 ) as a

MIN to get the minimum record from the set.

as a for the aliasing of the set of records like here the result of the inner query is aliasing "as a".

Final Output

The output will be 39000.