Solved

SQL query to find second maximum salary in each department of

Posted on 2011-03-01
8
1,206 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Trigger selecting another database 4 42
INSERT DATE FROM STRING COLUMN 18 63
CROSS APPLY 4 61
Replication failure 1 19
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…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

740 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