How would I find the second largest salary from the employee table? [closed]

It's difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. For help clarifying this question so that it can be reopened, visit the help center. Closed 9 y开发者_JAVA百科ears ago.

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

---

Try this:

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

---

Simple Answer:

SELECT distinct(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;

---

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.

---

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.

---

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

---

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

---

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

Yes running man.

---

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

---

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

---

Try this:

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

Then just get the second row.

---

   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

---

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

---

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

---

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)

---

Try this:

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

---

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*

---

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