Although the second highest salary is not as high as the one we saw from the query, it still says that there is a second-highest salary. Not only that, but the salary of $74200 in column number 4 is higher than the average salary of $56000 in column number 5 for all employees.

Display 2nd Highest Salary In Sql

Nth Highest salary

Finding the Nth highest salary( 2^nd, 3^rd, or n^th highest) in a table is the most important and common question asked in various interviews.

Here we will show you the best and easiest way to write SQL queries to find nth highest salary in a table.

To show this, we are using Table Emp having employee details like EID, ENAME, and SALARY. Data present in the Emp Table is shown below:

Table Name: Emp

EID	ENAME	SALARY
1	Amit	20000
2	Bhaskar	30000
3	Chandan	25000
4	Durgesh	28000
5	Parul	30000
6	Garima	25000
7	Akshita	28000
8	Sonu	40000
9	Ravi	37000
10	Rajesh	320000

The SQL query to calculate second highest salary in database table name as Emp

Query: 1

1. SQL> select min(salary) from   
2. (select distinct salary from emp order by salary desc)   
3. where rownum < 3;  
4.   
5. In order to calculate the second highest salary use rownum < 3  
6. In order to calculate the third highest salary use rownum < 4  

Output:

MIN(SALARY)
-----------
      37000

The Structure and data in Emp Table

The Output Screen

Let us understand how this query is working:

1. As this query is nested query lets understand each part step by step:  
2. Step 1: First this part of the query will get executed then the outer part of the query will act on the result produced by this query:            select distinct salary from emp order by salary desc  
3. As you can see that few employees are getting the same salary (for example Bhaskar, Parul, and Chandan, Garima are getting the same salary, therefore we have used distinct keyword, order by salary desc will arrange salary in descending order.  
4. The output of select distinct salary from emp order by salary desc  
5.     SALARY  
6. ———-  
7.      40000  
8.      37000  
9.      32000  
10.      30000  
11.      28000  
12.      25000  
13.      20000  

Introduction

Get a second highest salary using this SQL query.

Display 2nd Highest Salary In Sql

To display the 2nd highest salary in SQL, you can use the following query:

SELECT TOP 1 * FROM emp ORDER BY empno DESC, sal DESC LIMIT 1

SELECT * FROM `emp` WHERE `sal`< (SELECT MAX(`sal`) FROM `emp`) ORDER BY `sal` DESC LIMIT 1;

This SQL query is used to find the 2nd highest salary in the `emp` table. It is a simple SELECT statement where we have a WHERE clause that says `sal`< (SELECT MAX(`sal`) FROM `emp`). The WHERE clause is followed by an ORDER BY clause, which specifies that we want to order our results in descending order based on salary. Then, there’s another LIMIT 1 clause at the end, which means that there will only be one row returned from this query.

This approach works well on any relational database system.

This approach works well on any relational database system. Using the same approach to find any value in the data set, you can use this method to find the highest or lowest value, as well as averages.

Conclusion

There are many advantages to this method. Not on