This is a great question and one that we’ve had to tackle in the past. We’ll share some of the things we learned and then show you how to get the answer yourself.

First, let’s start with the basics: what’s the highest salary?

For this example, we’ll use a dataset from [source]. The dataset contains information about the salaries of employees at a company. To get started, let’s open up RStudio and import our dataset using read_csv(). Then, let’s calculate which employee has the highest salary by using sort() on our data frame.

Find 2nd Highest Salary

To Find the Third Highest Salary Using a Sub-Query,

1. SELECT TOP 1 SALARY.
2. FROM (
3. SELECT DISTINCT TOP 3 SALARY.
4. FROM tbl_Employees.
5. ORDER BY SALARY DESC.

Name     Salary
---------------
abc     100000
bcd     1000000
efg     40000
ghi     500000

How to find the employee whose salary is second highest. For example, in above table, “ghi” has the second highest salary as 500000. 

Below is simple query to find the employee whose salary is highest. 

  select *from employee where salary=(select Max(salary) from employee);

We can nest the above query to find the second largest salary. 

select *from employee 
group by salary 
order by  salary desc limit 1,1;

There are other ways :

SELECT name, MAX(salary) AS salary 
FROM employee 
WHERE salary IN
(SELECT salary FROM employee MINUS SELECT MAX(salary) 
FROM employee); 

SELECT name, MAX(salary) AS salary 
FROM employee 
WHERE salary <> (SELECT MAX(salary) 
FROM employee);

IN SQL Server using Common Table Expression or CTE, we can find the second highest salary: 

WITH T AS
(
SELECT *
   DENSE_RANK() OVER (ORDER BY Salary Desc) AS Rnk
FROM Employees
)
SELECT Name
FROM T
WHERE Rnk=2;

How to find the third largest salary? 
Simple, we can do one more nesting.  

SELECT name, MAX(salary) AS salary
  FROM employee
 WHERE salary < (SELECT MAX(salary) 
                 FROM employee
                 WHERE salary < (SELECT MAX(salary)
                 FROM employee)
                ); 

Note that instead of nesting for second, third, etc largest salary, we can find nth salary using general query like in MySQL: 

SELECT salary 
FROM employee 
ORDER BY salary desc limit n-1,1

SELECT name, salary
FROM employee A
WHERE n-1 = (SELECT count(1) 
             FROM employee B 
             WHERE B.salary>A.salary)

If multiple employee have same salary. 
Suppose you have to find 4th highest salary 

SELECT * FROM employee 
WHERE salary= (SELECT DISTINCT(salary) 
FROM employee ORDER BY salary LIMIT 3,1);

Generic query will be 

SELECT * FROM employee 
WHERE salary= (SELECT DISTINCT(salary) 
FROM employee ORDER BY salary DESC LIMIT n-1,1);

1. ) RESULT.
2. ORDER BY SALARY.