How to Find the Employee with the Second Highest Salary

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));

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);

HiveQL to find the second largest salary from the employee table?

In case of ties the accepted answer won't work. So below is my code which works in all situations. Just replaced row_number with dense_rank that's it. Want to know more about dense_rank then visit this link

select * from  (SELECT dep_name,salary,DENSE_RANK() over(ORDER BY salary desc) as rank FROM department) as A where rank = 2;

OUTPUT:

+--------+------+----------+
|dep_name|salary|     rank |
+--------+------+----------+
|      CS| 30000|         2|
|   CIVIL| 30000|         2|
+--------+------+----------+  

Hope it helps!

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

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;

I want to query difference between first and second highest salary

Use the DENSE_RANK (if you want highest two unique salary values) or ROW_NUMBER (if you want the salary of the two highest earning people regardless of ties) analytic functions and then use conditional aggregation:

SELECT MAX( CASE WHEN sal_rank = 1 THEN sal END )
         - MAX( CASE WHEN sal_rank = 2 THEN sal END ) AS sal_difference_ignore_ties,
       MAX( CASE WHEN sal_rownum = 1 THEN sal END )
         - MAX( CASE WHEN sal_rownum = 2 THEN sal END ) AS sal_difference_with_ties
FROM   (
  SELECT e.deptno,
         e.sal,
         DENSE_RANK() OVER ( ORDER BY e.sal DESC ) AS sal_rank,
         ROW_NUMBER() OVER ( ORDER BY e.sal DESC ) AS sal_rownum
  FROM   emp e
         INNER JOIN dept d
         ON ( e.deptno = d.deptno )
  WHERE  d.loc = 'New York'
);

Which, for the sample data:

CREATE TABLE emp ( deptno, sal ) AS
SELECT 1, 2000 FROM DUAL UNION ALL
SELECT 1, 2000 FROM DUAL UNION ALL
SELECT 1, 1000 FROM DUAL UNION ALL
SELECT 1,  500 FROM DUAL UNION ALL
SELECT 1,  200 FROM DUAL UNION ALL
SELECT 2, 2000 FROM DUAL UNION ALL
SELECT 2, 1500 FROM DUAL UNION ALL
SELECT 2,  200 FROM DUAL UNION ALL
SELECT 2,  700 FROM DUAL;

CREATE TABLE dept ( deptno, loc ) AS
SELECT 1, 'New York' FROM DUAL UNION ALL
SELECT 2, 'Beijing'  FROM DUAL;

Outputs:

SAL_DIFFERENCE_IGNORE_TIES | SAL_DIFFERENCE_WITH_TIES
-------------------------: | -----------------------:
                      1000 |                        0

---

Or, if you want all the departments:

SELECT deptno,
       MAX( CASE WHEN sal_rank = 1 THEN sal END )
         - MAX( CASE WHEN sal_rank = 2 THEN sal END ) AS sal_difference_ignore_ties,
       MAX( CASE WHEN sal_rownum = 1 THEN sal END )
         - MAX( CASE WHEN sal_rownum = 2 THEN sal END ) AS sal_difference_with_ties
FROM   (
  SELECT e.deptno,
         e.sal,
         DENSE_RANK() OVER ( PARTITION BY e.deptno ORDER BY e.sal DESC ) AS sal_rank,
         ROW_NUMBER() OVER ( PARTITION BY e.deptno ORDER BY e.sal DESC ) AS sal_rownum
  FROM   emp e
         INNER JOIN dept d
         ON ( e.deptno = d.deptno )
)
GROUP BY deptno;SELECT deptno,
       MAX( CASE WHEN sal_rank = 1 THEN sal END )
         - MAX( CASE WHEN sal_rank = 2 THEN sal END ) AS sal_difference_ignore_ties,
       MAX( CASE WHEN sal_rownum = 1 THEN sal END )
         - MAX( CASE WHEN sal_rownum = 2 THEN sal END ) AS sal_difference_with_ties
FROM   (
  SELECT e.deptno,
         e.sal,
         DENSE_RANK() OVER ( PARTITION BY e.deptno ORDER BY e.sal DESC ) AS sal_rank,
         ROW_NUMBER() OVER ( PARTITION BY e.deptno ORDER BY e.sal DESC ) AS sal_rownum
  FROM   emp e
         INNER JOIN dept d
         ON ( e.deptno = d.deptno )
)
GROUP BY deptno;

Which outputs:

DEPTNO | SAL_DIFFERENCE_IGNORE_TIES | SAL_DIFFERENCE_WITH_TIES
-----: | -------------------------: | -----------------------:
     1 |                       1000 |                        0
     2 |                        500 |                      500

db<>fiddle here

---

Related Topics