Get the second highest value in a MySQL table

I have a table of employees and salarie开发者_C百科s defined that way:

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

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

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

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

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

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)

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

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.

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

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.

The simple solution is as given below in query:

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

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

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

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 DISTINCT Salary
FROM emp
ORDER BY salary DESC
LIMIT 1 , 1

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

To get the second highest salary just use the below query

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

To get second highest value:

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

SELECT name, salary 
FROM employees 
where
 salary = (SELECT (salary) FROM employees GROUP BY salary DESC LIMIT 1,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;

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

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

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

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

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

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)

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.

Try this :

SELECT DISTINCT(`salary`)
    FROM `employee`

    ORDER BY `salary` DEC
    LIMIT 1,1

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

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.

Try this :

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