Get the second highest value in a MySQL table

Question

I have a table of employees and salaries defined that way:

"name" (type: VARCHAR)
"salary" (type: INTEGER)

What query can I use to get the second highest salary in this table?

What if you have 2 or more names with the same highest or 2nd highest salary? — Pentium10, Mar 19 '11 at 10:23

Dawson · Accepted Answer · 2014-05-20T13:41:34.100

62

Here's one that accounts for ties.

Name    Salary
Jim       6
Foo       5
Bar       5
Steve     4

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

Result --> Bar 5, Foo 5

EDIT: I took Manoj's second post, tweaked it, and made it a little more human readable. To me n-1 is not intuitive; however, using the value I want, 2=2nd, 3=3rd, etc. is.

/* looking for 2nd highest salary -- notice the '=2' */
SELECT name,salary FROM employees
WHERE salary = (SELECT DISTINCT(salary) FROM employees as e1
WHERE (SELECT COUNT(DISTINCT(salary))=2 FROM employees as e2
WHERE e1.salary <= e2.salary)) ORDER BY name

Result --> Bar 5, Foo 5

edited May 20 '14 at 13:41

answered Mar 19 '11 at 10:03

Dawson

7,567
1
26
25

1

What shall i do if i want to select thrird highest in from my table...Thanks in advance – jack Feb 17 '12 at 06:43
4

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))). This technique is just nesting SELECT statements to the point at which you're trying to find a number (2nd, 3rd, 4th highest paid). If I wanted to find the 5th highest paid employee AND account for ties, I would add an additional 3 more SELECT MAX(salary) FROM employees WHERE salary < ... etc. to the original answer's code. – Dawson Mar 12 '12 at 05:42
Wow.Amazing this query give exact result – Ganesan J Oct 27 '21 at 09:01

Mr.M · Answer 2 · 2013-09-19T05:45:26.680

54

A straight forward answer for second highest salary

SELECT name, salary
FROM employees ORDER BY `employees`.`salary` DESC LIMIT 1 , 1

another interesting solution

SELECT salary 
FROM emp 
WHERE salary = (SELECT DISTINCT(salary) 
                FROM emp as e1 
                WHERE (n) = (SELECT COUNT(DISTINCT(salary)) 
                             FROM emp as e2 
                             WHERE e1.salary <= e2.salary))

edited Sep 19 '13 at 05:45

answered Nov 08 '12 at 09:37

Mr.M

684
6
8

3

Why is this getting up-voted? That SELECT statement only returns one value: the TOP Salary. The question was to find the 2nd highest salary. – Dawson Jun 13 '13 at 20:13
5

This answer is getting up-voted because the first solution is the most straightforward solution. See the section on "offset" in the [MySQL docs on SELECT](http://dev.mysql.com/doc/refman/5.0/en/select.html). The first number after "LIMIT" is the offset when using the comma. The offset is zero-indexed. So, this query selects one row, starting at the second-highest. (though, this does not account for ties) – Jon Page Dec 31 '13 at 20:23
@Jon - check his edit. What it was doing in June, is NOT what it was doing in December. – Dawson Jan 20 '14 at 12:09
1

Actually, your first query wont get accurate results. Consider the situation where more than one employee with second highest salary. Your query will return only one row. – Sreeraj May 28 '15 at 10:17
1

1st query won't work if more than 1 employee has the same highest salary or the same 2nd highest salary. – jamesdeath123 Dec 18 '18 at 05:35

Sunny Sharma · Answer 3 · 2018-12-18T07:30:41.157

24

Seems I'm much late to answer this question. How about this one liner to get the same output?

SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1,1 ;

sample fiddle: https://www.db-fiddle.com/f/v4gZUMFbuYorB27AH9yBKy/0

edited Dec 18 '18 at 07:30

answered Mar 21 '14 at 09:00

Sunny Sharma

4,688
5
35
73

The `DISTINCT` is redundant here as the limit is already set to 1. – Raj Rajeshwar Singh Rathore Dec 17 '18 at 05:45
5

DISTINCT is not redundant. It is required as there may be two similar salaries one after other. – Sunny Sharma Dec 18 '18 at 06:35
Even if we have duplicate salaries still we will get only one row as the `limit` is defined as 1. So the `limit` makes the size as one row only so the `DISTINCT` becomes redundant. – Raj Rajeshwar Singh Rathore Dec 18 '18 at 07:18
2

I think you just have started, check out this sample fiddle: https://www.db-fiddle.com/f/v4gZUMFbuYorB27AH9yBKy/0 the second query "without `DISTINCT`" returns wrong value. – Sunny Sharma Dec 18 '18 at 07:30
1

Got your point now. Thanks a lot for the fiddle and the explanation. – Raj Rajeshwar Singh Rathore Dec 18 '18 at 09:56

score 9 · Answer 4 · answered Mar 19 '11 at 08:54

9

create table svalue (
name varchar(5),
value int
) engine = myisam;

insert into svalue value ('aaa',30),('bbb',10),('ccc',30),('ddd',20);

select * from svalue where value = (
select value 
from svalue
group by value
order by  value desc limit 1,1)

answered Mar 19 '11 at 08:54

Nicola Cossu

54,599
15
92
98

score 4 · Answer 5 · answered Nov 30 '12 at 12:16

4

FOR SECOND LAST:

SELECT name, salary
    FROM employee 
    ORDER BY salary DESC
    LIMIT 1 , 1

FOR THIRD LAST:

SELECT name, salary
    FROM employee 
    ORDER BY salary DESC
    LIMIT 2 , 1

answered Nov 30 '12 at 12:16

sandeepKumar

771
2
12
33

What if you have 2 or more names with the same highest or 2nd highest salary? Your solution would not work. You have to either use `DISTINCT` or `GROUP BY` And update your query – TarangP Feb 13 '23 at 06:05

score 4 · Answer 6 · edited Apr 29 '15 at 07:42

4

You can use this below mentioned query

SELECT emp.name, emp.salary 
FROM employees emp 
WHERE 2 = (SELECT COUNT(DISTINCT salary) 
           FROM employees 
           WHERE emp.salary<=salary
          );

You can change 2 to your desired highest record.

edited Apr 29 '15 at 07:42

Saharsh Shah

28,687
8
48
83

answered Nov 04 '14 at 10:10

Vishnu

3,899
2
18
19

score 3 · Answer 7 · edited Nov 03 '12 at 06:29

3

simple solution

SELECT * FROM TBLNAME ORDER BY COLNAME ASC LIMIT (n - x), 1

Note: n = total number of records in column

  x = value 2nd, 3rd, 4th highest etc

e.g

//to find employee with 7th highest salary

n = 100
x = 7

SELECT * FROM tbl_employee ORDER BY salary ASC LIMIT 93, 1

hope this helps

edited Nov 03 '12 at 06:29

Brad Werth

17,411
10
63
88

answered Nov 03 '12 at 06:11

Sonia G

97
4

What if you have 2 or more names with the same highest or 2nd highest salary? Your solution would not work. You have to either use `DISTINCT` or `GROUP BY` And update your query – TarangP Feb 13 '23 at 06:06

score 3 · Answer 8 · edited May 11 '13 at 05:06

3

Found another interesting solution

SELECT salary 
FROM emp 
WHERE salary = (SELECT DISTINCT(salary) 
                FROM emp as e1 
                WHERE (n) = (SELECT COUNT(DISTINCT(salary)) 
                             FROM emp as e2 
                             WHERE e1.salary <= e2.salary))

Sorry. Forgot to write. n is the nth number of salary which you want.

edited May 11 '13 at 05:06

Grijesh Chauhan

57,103
20
141
208

answered Dec 13 '12 at 05:23

Mr.M

684
6
8

SELECT salary FROM emp WHERE salary = (SELECT DISTINCT(salary) FROM emp as e1 WHERE (n) = (SELECT COUNT(DISTINCT(salary)) FROM emp as e2 WHERE e1.salary <= e2.salary)). Thsi will be the direct "n" substitution solution – Mr.M Jan 18 '13 at 05:20

score 3 · Answer 9 · edited Aug 04 '14 at 13:00

3

SELECT DISTINCT Salary
FROM emp
ORDER BY salary DESC
LIMIT 1 , 1

This query will give second highest salary of the duplicate records as well.

edited Aug 04 '14 at 13:00

Davide Aversa

5,628
6
28
40

answered Aug 04 '14 at 12:49

Rajat Gusain

31
1

score 3 · Answer 10 · edited Jul 18 '12 at 20:34

3

To display records having second largest value of mark:

SELECT username, mark
FROM tbl_one
WHERE mark = (
    SELECT DISTINCT mark
    FROM tbl_one
    ORDER by mark desc
    LIMIT 1,1
);

edited Jul 18 '12 at 20:34

Taz

3,718
2
37
59

answered Sep 22 '11 at 05:26

SUJI KUMAR.T.S

31
2

score 2 · Answer 11 · edited Apr 24 '13 at 07:52

2

The simple solution is as given below in query:

select max(salary) as salary from employees where salary<(select max(salary) from employees);

edited Apr 24 '13 at 07:52

Ankur

5,086
19
37
62

answered Apr 24 '13 at 07:31

Mostafizur Rahman Prince

21
2

score 2 · Answer 12 · answered Jun 28 '13 at 16:50

for 2nd heightest salary

 select max(salary) from salary where salary not in (select top 1 salary from salary order by salary desc)

for 3rd heightest salary

 select max(salary) from salary where salary not in (select top 2 salary from salary order by salary desc)

and so on......

score 2 · Answer 13 · answered Jun 29 '13 at 07:37

2

SELECT MAX(salary) salary
FROM tbl
WHERE salary <
  (SELECT MAX(salary)
   FROM tbl);

answered Jun 29 '13 at 07:37

nvogel

24,981
1
44
82

score 2 · Answer 14 · answered Jan 28 '14 at 09:58

To get the *N*th highest value, better to use this solution:

SELECT * FROM `employees`  WHERE salary =
         (SELECT DISTINCT(salary) FROM `employees` 
         ORDER BY salary DESC LIMIT {N-1},1);

or you can try with:

SELECT * FROM `employees` e1 WHERE 
        (N-1) = (SELECT COUNT(DISTINCT(salary)) 
        FROM `employees` e2 
        WHERE e1.salary < e2.salary );

N=2 for second highest N=3 for third highest and so on.

score 2 · Answer 15 · edited Jan 06 '17 at 11:49

2

To get the second highest salary just use the below query

SELECT salary FROM employees
ORDER BY salary DESC LIMIT 1,1;

edited Jan 06 '17 at 11:49

denny

2,084
2
15
19

answered Dec 11 '14 at 06:17

jeswin

382
4
9

What if you have 2 or more names with the same highest or 2nd highest salary? Your solution would not work. You have to either use `DISTINCT` or `GROUP BY` And update your query – TarangP Feb 13 '23 at 06:06

score 2 · Answer 16 · answered Jul 11 '17 at 06:03

2

To get second highest value:

SELECT `salary` FROM `employees` ORDER BY `salary` DESC LIMIT 1, 1;

answered Jul 11 '17 at 06:03

Nitish Kumar Diwakar

663
4
14
25

What if you have 2 or more names with the same highest or 2nd highest salary? Your solution would not work. You have to either use `DISTINCT` or `GROUP BY` And update your query – TarangP Feb 13 '23 at 06:06

score 2 · Answer 17 · answered Nov 30 '17 at 14:01

2

SELECT name, salary 
FROM employees 
where
 salary = (SELECT (salary) FROM employees GROUP BY salary DESC LIMIT 1,1)

answered Nov 30 '17 at 14:01

Niklesh_Chauhan

647
5
16

score 1 · Answer 18 · edited Sep 19 '13 at 05:56

1

SELECT MIN(id) as id FROM students where id>(SELECT MIN(id) FROM students);

edited Sep 19 '13 at 05:56

RAS

8,100
16
64
86

answered Dec 21 '12 at 07:34

Ashish Ranjan

11
2

score 1 · Answer 19 · answered Feb 15 '13 at 16:06

1

Try this one to get n th max salary

i have tried this before posting & It Works fine

eg. to find 10th max salary replace limit 9,1;

mysql> select name,salary from emp group by salary desc limit n-1,1;

answered Feb 15 '13 at 16:06

Kiran

11
1

Pinkesh Sharma · Answer 20 · 2019-10-16T17:27:42.543

0

SELECT SALARY 
FROM (SELECT * 
      FROM EMPLOYEE 
      ORDER BY SALARY 
      DESC LIMIT ***2***) AS TOP_SALARY 
ORDER BY SALARY ASC 
LIMIT 1

edited Oct 16 '19 at 17:27

answered Feb 06 '13 at 12:35

Pinkesh Sharma

2,428
20
16

score 0 · Answer 21 · edited Sep 19 '13 at 05:56

0

SELECT name, salary
FROM EMPLOYEES
WHERE salary = ( 
SELECT DISTINCT salary
FROM EMPLOYEES
ORDER BY salary DESC 
LIMIT 1 , 1 )

edited Sep 19 '13 at 05:56

RAS

8,100
16
64
86

answered Apr 19 '13 at 11:37

Vipul bhojwani

366
4
7

score 0 · Answer 22 · answered Mar 21 '14 at 09:18

0

with alias as
(
select name,salary,row_number() over(order by salary desc ) as rn from employees
)
select name,salary from alias where rn=n--n being the nth highest salary

answered Mar 21 '14 at 09:18

Arun Peddakotla

163
8

score 0 · Answer 23 · answered May 20 '14 at 05:22

0

SELECT username, salary
FROM tblname
GROUP by salary
ORDER by salary desc
LIMIT 0,1 ;

answered May 20 '14 at 05:22

SagarPPanchal

9,839
6
34
62

score 0 · Answer 24 · edited Jan 06 '17 at 12:34

SELECT name, salary
FROM employees
order by salary desc limit 1,1

and this query should do your job. First we are sorting the table in descending way so the person with the highest salary is at the top, and the second highest is at the second position. Now limit a,b means skip the starting a elements and then print the next b elements. So you should use limit 1,1 in this case.

Hope this helps.

score 0 · Answer 25 · edited Apr 09 '15 at 07:36

0

SELECT name,salary FROM employee
WHERE salary = (SELECT DISTINCT(salary) FROM employee ORDER BY salary DESC LIMIT 1,1) ORDER BY name

edited Apr 09 '15 at 07:36

Jens

67,715
15
98
113

answered Apr 09 '15 at 07:33

Manjeet Singh Chauhan

161
1
5

Ashish Pathak · Answer 26 · 2017-07-11T05:54:00.743

0

Try this :

SELECT DISTINCT(`salary`)
    FROM `employee`

    ORDER BY `salary` DEC
    LIMIT 1,1

edited Jul 11 '17 at 05:54

answered Jun 23 '15 at 05:14

Ashish Pathak

827
8
16

score 0 · Answer 27 · answered Aug 27 '15 at 10:50

Get second, third, fourth......Nth highest salary using following query

SELECT MIN(salary) from employees WHERE salary IN( SELECT TOP N salary FROM employees ORDER BY salary DESC)

Replace N by you number i.e. N=2 for second highest salary, N=3 for third highest salary and so on. So for second highest salary use

SELECT MIN(salary) from employees WHERE salary IN( SELECT TOP 2 salary FROM employees ORDER BY salary DESC)

score 0 · Answer 28 · answered Aug 23 '22 at 10:20

0

select MIN(salary) from employee order by age desc limit 2; It sorts the column in descending order takes the top 2 and returns the minimum of them which is the second highest.

answered Aug 23 '22 at 10:20

Abhinav Mukherjee

40
8

Ashish · Answer 29 · 2016-01-20T07:36:45.187

-1

Try this :

Proc sql;
  select employee, salary
  from (select * from test having salary < max(salary)) 
   having salary = max(salary)
  ;
Quit;

edited Jan 20 '16 at 07:36

answered Jan 19 '16 at 17:41

Ashish

1
2

1

This is a very confusing answer - can you clarify a bit? – winhowes Jan 19 '16 at 17:53
for the 2nd Maximum, remove the maximum from table using nested query. then the table generated from nested query get fed to outer query where marks should be equal to max(marks), Mind to add group by statement to see department wise 2nd maximum. – Ashish Jan 19 '16 at 21:03
The field names don't even match the ones that were given in the question. It's possible there's a useful answer here, but if there is, then the OP should [edit] the answer to (a) clean up the sample code to reflect the given problem and (b) incorporate the explanatory text that's currently only in a comment. – shoover Jan 20 '16 at 01:04
Sorry for the confusion, I hoped discussing the logic would work. Anyway I have posted the code here. It returns the 2nd highest salary from table. – Ashish Jan 20 '16 at 07:41

Get the second highest value in a MySQL table

29 Answers29

Linked