how to get second highest salary department wise without using analytical functions?

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

Create table and insert dummy data

CREATE TABLE #Employee
(
 Id Int,
 Name NVARCHAR(10), 
 Sal int, 
 deptId int
)


INSERT INTO #Employee VALUES
(1, 'Ashish',1000,1),
(2,'Gayle',3000,1),
(3, 'Salman',2000,2),
(4,'Prem',44000,2)

Query to get result

 ;WITH cteRowNum AS (
SELECT *,
       DENSE_RANK() OVER(PARTITION BY deptId ORDER BY Sal DESC) AS RowNum
    FROM #Employee
 )
 SELECT *
 FROM cteRowNum
 WHERE RowNum = 2;