Query for second maximum salary from employee table

Question

how to find out second maximum salary from all employee in employee table ? please give me sql query.

Answer 1

Try something like:

SELECT TOP 1 compensation FROM (
  SELECT TOP 2 compensation FROM employees
  ORDER BY compensation DESC
) AS em ORDER BY compensation ASC

Essentially:

• Find the top 2 salaries in descending order.
• Of those 2, find the top salary in ascending order.
• The selected value is the second-highest salary.

Answer 2

select salary, employeeid from employees order by salary desc limit 2

Then just get the second row.

Answer 3

To use just ANSI SQL, Below is the query:

select max(salary) from emp where salary < (select max(salary) from emp)

Answer 4

May be you should use RANK

 SELECT  * FROM (
    SELECT [Salary],(DENSE_RANK() OVER 
        (ORDER BY [Salary] DESC)) AS rnk
      FROM [Table1] GROUP BY [Num]
    ) AS A
    WHERE A.rnk = 2

Answer 5

slect max(salary) from emptable where salary < (select max(salary) from emptable);

Answer 6

Depends on the version, you can use many methods Refer this http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/find-nth-maximum-value.aspx