Find the 3rd or Nth Highest Salary in a Table Via Co-Related Query

Before doing the start of this article, we need to understand what a Co-related Query is.
 
A Co-related Query is nothing but an inner query that depends on the outer query, where the outer query passes the record to the inner query and then the inner query executes itself on the basis of that record and then sends the output to the outer query.


 
Now back to the purpose of the article, how to find the Nth highest salary, we must create a table in the database for 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 for this demo.
 
Step 2: Insert some values in the table depending on 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')

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 2nd easiest way (to learn the easiest way, see the Find the 3rd or Nth Highest Salary in a Table via SubQuery which is very famous and compact).

Before getting the Nth Highest salary, we will check the salary of employees in decreasing order.

SELECT  Name,salary  FROM employee ORDER BY salary desc

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


  Explanation:  In the preceding query my purpsoe is to:

1. Take the salary one by one by outer query and pass it to the inner query.
2. Count those salaries which are greater than equal to that salary (passed by outer query)
3. The inner query sends the count to the outer query and matches it with the digit we obtained from the outer query like 3 because we want to find the 3rd highest salary.
4. If the output matches with the digit, it returns the Final output.

Note: Check Figure 1.1 to understand the preceding steps.
 
Now our query (in Figure 1.6) executes like:

1. The outer query will take 20000 and pass it to the inner query which determines how many records have a salary greater than or equal to 20000 and then the inner query returns 2 to the outer query because only 28000 and 20000 are greater than or equal to 20000. Now the outer query will match 3=2 that is false and it moves forward to the next record.
    
2. The outer query will take 10000 and pass it to the inner query which determines how many records have a salary greater than or equal to 10000 and then the inner query returns 4 to the outer query because all are greater than or equal to 10000. Now the outer query will match 3=4 that is false and it moves forward to the next record.
    
3. The outer query will take 28000 and pass it to the inner query which determines how many records have a salary greater than or equal to 28000 and then the inner query returns 1 to the outer query because only 28000 is greater than or equal to 28000. Now the outer query will match 3=1 that is false and it moves forward to the next record.
    
4. The outer query will take 15000 and pass it to the inner query which determines how many records have a salary greater than or equal to 15000 and then the inner query returns 1 to the outer query because 15000, 20000 and 280000 are all greater than or equal to 15000. Now the outer query will match 3=3 that is true and returns the data.

Final Output:  The output will be 15000.