Department wise nth max salary

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Department wise nth max salary

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-09-08 : 07:15:34

Marimuthu writes "i want a query that returns n th max salary of the employee with respective to their department

for eg.
if the emp table looks like this

empid empname empsal empdept
1 Alex 15000 1
2 Sonia 20000 1
3 Edwin 50000 1
4 Thomas 30000 2
5 Peter 20000 2
6 John 34000 2
7 Johnson 55000 3
8 Peterson 20300 3

for the 2nd max empsal the o/p should be like this

empid empname empsal empdept
2 Sonia 20000 1
4 Thomas 30000 2
8 Peterson 20300 3

mail me asap
Regards"

dsdeming

479 Posts

Posted - 2003-09-08 : 08:34:48

You need something like:

SET ROWCOUNT 2

SELECT TOP 1 t.empid, t.empsal
FROM ( SELECT * FROM emp ORDER BY empsal ) t
ORDER BY
t.empsal DESC

SET ROWCOUNT 0

Dennis

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-08 : 13:51:08

First, you need to decide how to handle ties. In this example, we will say that if the salary is the same, then the person with the lowest EmpID gets precedence.


select E.*, 
    (select count(*) 
     from 
       Salaries s2 
     where 
        s.empDept = s2.empDept and 
       (s2.empSal > s1.empSal OR 
             (s2.empSal = S1.EmpSal and 
              S2.EmpID <= S1.EmpID))) as DeptRank
FROM
  Salaries s1

Note how the confusing WHERE ranks each employee based on their salary in relation to their dept, and how it handles ties.

Then, to return the #2 salary from each dept, you just:


SELECT * 
FROM
 (aboveSQL) a
WHERE DeptRank = 2

- Jeff

dsdeming

479 Posts

Posted - 2003-09-09 : 08:17:12

Good point, Jeff. I wasn't handling ties at all. And to think, you did all that without using a cross join.

Dennis

only333
Starting Member

1 Post

Posted - 2011-06-01 : 04:10:15

quote:
Originally posted by jsmith8858

First, you need to decide how to handle ties. In this example, we will say that if the salary is the same, then the person with the lowest EmpID gets precedence.

select E.*,
(select count(*)
from
Salaries s2
where
s.empDept = s2.empDept and
(s2.empSal > s1.empSal OR
(s2.empSal = S1.EmpSal and
S2.EmpID <= S1.EmpID))) as DeptRank
FROM
Salaries s1

Note how the confusing WHERE ranks each employee based on their salary in relation to their dept, and how it handles ties.

Then, to return the #2 salary from each dept, you just:

SELECT *
FROM
(aboveSQL) a
WHERE DeptRank = 2

- Jeff

Amazing! Thanks a million Jeff for sharing.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-06-01 : 11:14:15

More methods
http://beyondrelational.com/blogs/madhivanan/archive/2008/09/12/return-top-n-rows.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources