[mysql] Get the second highest value in a MySQL table

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?

Try this :

SELECT DISTINCT(`salary`)
    FROM `employee`

    ORDER BY `salary` DEC
    LIMIT 1,1

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

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)

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)

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

Try this :

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

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.

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

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.

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

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

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

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

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

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.

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.

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

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.

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;

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

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

To get second highest value:

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

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

The simple solution is as given below in query:

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

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

To get the second highest salary just use the below query

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