How would I find the second largest salary from th

问题:

How would I go about querying for the second largest salary from all employees in my Employee table?

回答1:

Try this:

SELECT max(salary)
FROM emptable
WHERE salary < (SELECT max(salary)
                FROM emptable);

回答2:

Simple Answer:

SELECT sal
FROM emp
ORDER BY sal DESC
LIMIT 1, 1;

You will get only the second max salary.

And if you need any 3rd or 4th or Nth value you can increase the first value followed by LIMIT (n-1) ie. for 4th salary : LIMIT 3, 1;

回答3:

Most of the other answers seem to be db specific.

General SQL query should be as follows:

select
   sal 
from
   emp a 
where
   N = (
      select
         count(distinct sal) 
      from
         emp b 
      where
         a.sal <= b.sal
   )
where
   N = any value

and this query should be able to work on any database.

回答4:

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.

If the salaries aren't distinct, you can use SELECT DISTINCT TOP ... instead.

回答5:

Maybe you should use DENSE_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

回答6:

To find second max salary from employee,

SELECT MAX(salary) FROM employee
WHERE salary NOT IN (
    SELECT MAX (salary) FROM employee
)

To find first and second max salary from employee,

SELECT salary FROM (
    SELECT DISTINCT(salary) FROM employee ORDER BY salary DESC
) WHERE rownum<=2

This queries are working fine because i have used

回答7:

select max(Emp_Sal) 
from Employee a
where 1 = ( select count(*) 
         from Employee b
         where b.Emp_Sal > a.Emp_Sal)

Yes running man.

回答8:

//To select name of employee whose salary is second highest

SELECT name
FROM employee WHERE salary =
       (SELECT MIN(salary) FROM 
             (SELECT TOP (2) salary
              FROM employee
              ORDER BY salary DESC) )

回答9:

Try this:

SELECT
    salary,
    employeeid
FROM
    employees
ORDER BY
    salary DESC
LIMIT 2

Then just get the second row.

回答10:

select distinct(t1.sal) 
from emp t1 
where &n=(select count(distinct(t2.sal)) from emp t2 where t1.sal<=t2.sal);

Output: Enter value for n: if you want 2nd highest ,enter 2; if you want 5,enter n=3

回答11:

   select * from compensation where Salary = (
      select top 1 Salary from (
      select top 2 Salary from compensation 
      group by Salary order by Salary desc) top2
      order by Salary)

which will give you all rows with second highest salary, which a few people may share

回答12:

    select max(Salary) from Employee 
where Salary
 not in (Select Max(Salary) from Employee)

回答13:

select max(Salary) from Employee 
where Salary
  not in (Select top4 salary from Employee);

because answer is as follows

max(5,6,7,8)

so 5th highest record will be displayed, first four will not be considered

回答14:

select max(sal) from emp
where sal not in (select max(sal) from emp )

OR

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

回答15:

Try this:

select max(Emp_Sal) 
from Employee a
where 1 = ( select count(*) 
         from Employee b
         where b.Emp_Sal > a.Emp_Sal)

回答16:

SELECT
    TOP 1 salary
FROM
    (
        SELECT
            TOP 2 salary
        FROM
            employees
    ) sal
ORDER BY
    salary DESC;

回答17:

select * from emp 
where sal=(select min(sal) from 
(select sal from(select distinct sal from emp order by sal desc)
where rownum<=n));

n can be the value you want to see......

you can see all the fields of that person who having nth highest salary*strong text*