Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL query to find second maximum salary in each department of

Posted on 2011-03-01
8
Medium Priority
?
1,214 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
  • 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 25

Accepted Solution

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

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

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

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

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

963 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