Solved

SQL query to find second maximum salary in each department of

Posted on 2011-03-01
8
1,209 Views
Last Modified: 2012-05-11
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
0
Comment
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
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 4

Expert Comment

by:MarioAlcaide
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

by:Techyy
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

by:reb73
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

Open in new window

0
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
LVL 25

Accepted Solution

by:
reb73 earned 250 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

Open in new window

0
 
LVL 9

Expert Comment

by:mayank_joshi
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

by:mayank_joshi
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

Open in new window

0
 

Author Comment

by:mu_ravi1
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

by:mu_ravi1
ID: 35015038
excellent
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

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…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question