How to get all employee name who have 2nd highest salary – MySQL

MySQL is an open-source relational database management system.
Its name is a combination of “My”, the name of co-founder Michael Widenius’s daughter, and “SQL”, the abbreviation for Structured Query Language.

Let’s see the query how we get the all employee name having 2nd highest salary in the organization.

First let’s suppose there is “Emp” table where employee records are stored.

+----+--------+------
| id | name | salary |
+----+--------+-------
| 1  | avi  | 10000  |
| 2  | saas | 20000  |
| 3  | jhon | 30000  |
| 4  | deo  | 20000  |
| 5  | san  | 10000  |
+----+----------------

There are several way we can get the required result.But in this post i’ll show you the best way.

Let’s do with me-

SELECT id,name, salary FROM Emp e WHERE 2 = (SELECT COUNT(DISTINCT salary) FROM Emp p WHERE e.salary<=p.salary);

Output :

+----+--------+------ 
| id | name | salary |
+----+--------+------- 
| 2 | saas | 20000 | 
| 4 | deo  | 20000 | 
+----+----------------

Related posts:

Duplicate rows in MySql