Solved

SQL query to find second maximum salary in each department of

Posted on 2011-03-01
8
1,207 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server 2012 express 24 44
SQL 2005 - Memory Table Column Names 11 87
Sql query 107 101
Query to return total 6 26
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

734 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