mu_ravi1
asked on
SQL query to find second maximum salary in each department of
i have a dept table which contains
deptno deptname empid salary
1 sales 10 1000
2 libray 11 2000
3 IT 12 5000
4 sales 14 6000
5 libray 15 5000
6 IT 16 9000
7 IT 17 19000
8 libray 18 7000
pls provide me the query to find the second max sal in each department
thanks inadvance
ravi
deptno deptname empid salary
1 sales 10 1000
2 libray 11 2000
3 IT 12 5000
4 sales 14 6000
5 libray 15 5000
6 IT 16 9000
7 IT 17 19000
8 libray 18 7000
pls provide me the query to find the second max sal in each department
thanks inadvance
ravi
SELECT max(salary) FROM dept WHERE salary< ( SELECT max(salary) FROM dept);
or
SELECT max(salary) FROM dept WHERE salary NOT IN (SELECT max(salary) FROM dept);
or
SELECT max(salary) FROM dept WHERE salary NOT IN (SELECT max(salary) FROM dept);
Try -
;with cte (rowno, deptname, empid, salary)
as
(select row_number() over (partition by deptname order by salary desc) as rowno
,deptname, empid, salary
)
select * from cte where rowno = 2ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
select max(e1.salary) from dept e1
where e1.Salary NOT IN(
select Max(e2.salary) from dept e2
group by e2.deptname
)
group by e1.deptname
where e1.Salary NOT IN(
select Max(e2.salary) from dept e2
group by e2.deptname
)
group by e1.deptname
better:-
select e1.deptname, max(e1.salary) from dept e1
where e1.Salary NOT IN(
select Max(e2.salary) from dept e2
group by e2.deptname
)
group by e1.deptnameASKER
Hi
the query with cte is working perfectly.
thanks reb.
is there any other way without using cte.
The rest of queries are giving wrong result.
pls help
the query with cte is working perfectly.
thanks reb.
is there any other way without using cte.
The rest of queries are giving wrong result.
pls help
ASKER
excellent
The query will be something like this:
select * from dept
where salary in ( select min(salary) from
(select salary from dept
order by salary desc)
where rownum <=2 )