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

User · Answer

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

User · Answer

SELECT name  salary FROM EMPLOYEES WHERE salary      SELECT DISTINCT salary FROM EMPLOYEES ORDER BY salary DESC  LIMIT 1   1

User · Answer

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

User · Answer

SELECT name  salary  FROM employees  where  salary    SELECT  salary  FROM employees GROUP BY salary DESC LIMIT 1 1

User · Answer

Try this one to get n th max salary  i have tried this before posting  amp  It Works fine  eg  to find 10th max salary replace limit 9 1   mysql gt  select name salary from emp group by salary desc limit n-1 1

User · Answer

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

User · Answer

Try this        SELECT DISTINCT  salary       FROM  employee       ORDER BY  salary  DEC     LIMIT 1 1

User · Answer

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  lt   e2 salary     Sorry  Forgot to write  n is the nth number of salary which you want

User · Answer

To get second highest value   SELECT  salary  FROM  employees  ORDER BY  salary  DESC LIMIT 1  1

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

SELECT name salary FROM employee WHERE salary    SELECT DISTINCT salary  FROM employee ORDER BY salary DESC LIMIT 1 1  ORDER BY name

User · Answer

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  lt   SELECT MAX salary  FROM employees    Result -- gt  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  lt   e2 salary   ORDER BY name  Result -- gt  Bar 5  Foo 5

User · Answer

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  lt   e2 salary

User · Answer

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

User · Answer

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 lt  salary                You can change 2 to your desired highest record

User · Answer

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  lt  e2 salary       N 2 for second highest  N 3 for third highest and so on

User · Answer

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

User · Answer

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

User · Answer

SELECT MAX salary  salary FROM tbl WHERE salary  lt     SELECT MAX salary     FROM tbl

User · Answer

SELECT MIN id  as id FROM students where id gt  SELECT MIN id  FROM students

User · Answer

SELECT SALARY  FROM  SELECT          FROM EMPLOYEE        ORDER BY SALARY        DESC LIMIT    2     AS TOP SALARY  ORDER BY SALARY ASC  LIMIT 1

User · Answer

Try this    Proc sql    select employee  salary   from  select   from test having salary  lt  max salary       having salary   max salary      Quit

User · Answer

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

User · Answer

To get the second highest salary just use the below query  SELECT salary FROM employees ORDER BY salary DESC LIMIT 1 1

User · Answer

The simple solution is as given below in query    select max salary  as salary from employees where salary lt  select max salary  from employees

User · Answer

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

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

Examples related to mysql