How to Get Second-Highest Salary Employees in a Table

Get the name of the employee with the second highest salary

You can use

select name,sal from emp where sal = (select max(sal) from emp where sal < (select max(sal) from emp));

Find max and second max salary for a employee table MySQL

You can just run 2 queries as inner queries to return 2 columns:

select
  (SELECT MAX(Salary) FROM Employee) maxsalary,
  (SELECT MAX(Salary) FROM Employee
  WHERE Salary NOT IN (SELECT MAX(Salary) FROM Employee )) as [2nd_max_salary]

SQL Fiddle Demo

Getting second highest Salary value in MySQL

You can utilize LIMIT {[offset,] row_count}. Refer https://dev.mysql.com/doc/refman/8.0/en/select.html

Order by Salary in descending order, and get the second row by defining OFFSET as 1. We will use DISTINCT on Salary as there is a possibility to have multiple rows for the highest salary.

SELECT DISTINCT
  Salary
FROM Employee
ORDER BY Salary DESC
LIMIT 1,1

How to get second highest salaried employees in mysql

You can get it by this:

2nd Largest Salary:

SELECT name, salary
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees))

3rd Largest Salary:

SELECT name, salary
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees)))

Helpful links:
http://www.mysqltutorial.org/select-nth-highest-record-database-table-using-mysql.aspx
http://www.programmerinterview.com/index.php/database-sql/find-nth-highest-salary-sql/
http://www.coderanch.com/t/530503/JDBC/databases/select-Nth-highest-salary-table

How to get second-highest salary employees in a table

To get the names of the employees with the 2nd highest distinct salary amount you can use.

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

If Salary is indexed the following may well be more efficient though especially if there are many employees.

SELECT Name
FROM   Employees
WHERE  Salary = (SELECT MIN(Salary)
                 FROM   (SELECT DISTINCT TOP (2) Salary
                         FROM   Employees
                         ORDER  BY Salary DESC) T);

Test Script

CREATE TABLE Employees
  (
     Name   VARCHAR(50),
     Salary FLOAT
  )

INSERT INTO Employees
SELECT TOP 1000000 s1.name,
                   abs(checksum(newid()))
FROM   sysobjects s1,
       sysobjects s2

CREATE NONCLUSTERED INDEX ix
  ON Employees(Salary)

SELECT Name
FROM   Employees
WHERE  Salary = (SELECT MIN(Salary)
                 FROM   (SELECT DISTINCT TOP (2) Salary
                         FROM   Employees
                         ORDER  BY Salary DESC) T);

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

SELECT Name
FROM   Employees
WHERE  Salary = (SELECT DISTINCT TOP (1) Salary
                 FROM   Employees
                 WHERE  Salary NOT IN (SELECT DISTINCT TOP (1) Salary
                                       FROM   Employees
                                       ORDER  BY Salary DESC)
                 ORDER  BY Salary DESC)

SELECT Name
FROM   Employees
WHERE  Salary = (SELECT TOP 1 Salary
                 FROM   (SELECT TOP 2 Salary
                         FROM   Employees
                         ORDER  BY Salary DESC) sel
                 ORDER  BY Salary ASC)  

Second Highest Salary

In case of ties you want the second highest distinct value. E.g. for values 100, 200, 300, 300, you want 200.

So get the highest value (MAX(salary) => 300) and then get the highest value less than that:

select max(salary) from mytable where salary < (select max(salary) from mytable);

How to get 2nd highest salary of each employee in employee table which contains more than one entry in employee table

inner query will return employees with their highest salary and then from out query those highest salaries will be filtered out
so you will get the second highest salary

 SELECT MAX(T.salery),T.NAME FROM TABLE T
     INNER JOIN (SELECT MAX(salery),NAME FROM TABLE GROUP BY NAME) TT 
      ON TT.NAME=T.NAME AND TT.SALERY!= T.SALERY 
       GROUP BY T.NAME;

example

mysql> SELECT * FROM payments;
+----+------------+---------+-------+
| id | date       | user_id | value |
+----+------------+---------+-------+
|  1 | 2016-06-22 |       1 |    10 |
|  2 | 2016-06-22 |       3 |    15 |
|  3 | 2016-06-22 |       4 |    20 |
|  4 | 2016-06-23 |       2 |   100 |
|  5 | 2016-06-23 |       1 |   150 |
|  6 | 2016-06-23 |       2 |   340 |
+----+------------+---------+-------+
6 rows in set (0.00 sec)

mysql> select max(value),user_id from payments group by user_id;
+------------+---------+
| max(value) | user_id |
+------------+---------+
|        150 |       1 |
|        340 |       2 |
|         15 |       3 |
|         20 |       4 |
+------------+---------+
4 rows in set (0.00 sec)

mysql> select max(T.value),TT.user_id from payments T inner join (select max(value) as val,user_id from payments group by user_id) TT on T.value!=TT.val and T.user_id=TT.user_id group by T.user_id;;
+--------------+---------+
| max(T.value) | user_id |
+--------------+---------+
|           10 |       1 |
|          100 |       2 |
+--------------+---------+
2 rows in set (0.00 sec)

Selecting the record(s) with the second highest something

Window functions are the built-in functionality to do this. In particular, dense_rank():

select e.*
from (select e.*, dense_rank() over (order by salary desc) as seqnum
      from employee e
     ) e
where seqnum = 2;

---

Related Topics