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)