how to get second highest salary department wise w

Question

Suppose we have 3 employees in each department.we have total 3 departments . Below is the sample source table

Emp deptno salary
A    10     1000
B    10     2000
C    10     3000
D    20     7000
E    20     9000
F    20     8000
G    30     17000
H    30     15000
I    30     30000

Output

B    10     2000
F    20     8000
G    30     17000

With using analytic function dense_rank we can achive the second highest salary dept wise.

Can we achieve this without using ANY analytic function ???

Is Max() is also analytic function ??

Answer 1

It is a pain, but you can do it. The following query gets the second highest salary:

select t.deptno, max(t.salary) as maxs
from table t
where t.salary < (select max(salary)
                  from table t2
                  where t2.deptno = t.deptno
                 )
group by t.deptno;

You can then use this to get the employee:

select t.*
from table t join
     (select t.deptno, max(t.salary) as maxs
      from table t
      where t.salary < (select max(salary)
                        from table t2
                        where t2.deptno = t.deptno
                       )
      group by t.deptno
     ) tt
     on t.deptno = tt.deptno and t.salary = tt.maxs;

Answer 2

Quite straightforward and declarative, but slow

select 
  t1.*
from 
  #tmp t1
  inner join #tmp h1 on h1.dept = t1.dept and h1.emp <> t1.emp
  left outer join #tmp h2 on h2.dept = h1.dept and h2.salary > h1.salary
  left outer join #tmp t2 on t1.dept = t2.dept and t2.salary > t1.salary and t2.emp <> h1.emp
where 
  t2.emp is null and h2.emp is null

Answer 3

This will give you 2nd highest salary in each department:

SELECT Emp, deptno, salary
FROM Emp a
WHERE 1 = (SELECT COUNT(DISTINCT salary) 
        FROM Emp b 
        WHERE b.salary > a.salary AND a.deptno = b.deptno)

Answer 4

select min(salary),deptno from (SELECT distinct top 2 salary,deptno from table ORDER BY salary DESC) as a group by deptno

Answer 5

You can find 2nd highest salary something like this:

select max(a.Salary),a.Deptno from Employee a join (select MAX(salary) salary 
from Employee group by Deptno) b on a.Salary < b.salary group by a.Deptno

And no MAX() is not an analytic function.

Reference

Answer 6

Very simple logic.

Please try:

SELECT dept as dd, ( SELECT ee.salary FROM `employees` as ee WHERE ee.dept=dd 
ORDER BY ee.salary DESC LIMIT 1,1 ) as sndHigh 
FROM `employees` 
WHERE 1 
GROUP BY dept