Get the second highest value in a MySQL table

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


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

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

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)

Tags:

Mysql