Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# SQL query to find second maximum salary in each department of

Posted on 2011-03-01
Medium Priority
1,211 Views
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
ravi
0
Question by:mu_ravi1
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 2
• 2
• 2
• +2

LVL 4

Expert Comment

ID: 35006588
Hi:

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

LVL 1

Expert Comment

ID: 35006594
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);
0

LVL 25

Expert Comment

ID: 35006600
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 = 2
``````
0

LVL 25

Accepted Solution

reb73 earned 1000 total points
ID: 35006613
Sorry missing from clause -
``````;with cte (rowno, deptname, empid, salary)
as
(select row_number() over (partition by deptname order by salary desc) as rowno
,deptname, empid, salary
from	dept
)
select deptname, empid, salary from cte where rowno = 2
``````
0

LVL 9

Expert Comment

ID: 35006689
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
0

LVL 9

Expert Comment

ID: 35006705
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.deptname
``````
0

Author Comment

ID: 35014433
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
0

Author Closing Comment

ID: 35015038
excellent
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
###### Suggested Courses
Course of the Month11 days, 11 hours left to enroll