How to get Top 3 and bottom 3 salaries

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

General SQL Server Forums

New to SQL Server Programming

How to get Top 3 and bottom 3 salaries

Author

Topic

archanasql
Starting Member

27 Posts

Posted - 2008-03-13 : 02:25:43

Hi,

I have a table like this..

empno empname dept sal
----------- ------------------------------ -------------------- -
1 mike HR 4000
2 John HR 2000
3 Patty HR 3500
4 Carly HR 2200
5 ABC HR 1000
6 DEF HR 4500
7 Ben HR 2200
8 HGI IT 4000
9 GOG IT 2000
10 LILY IT 3500
11 Cathy IT 2200
12 Paul IT 1000
13 Ben IT 4500
14 Ben Fin 2200
15 HGI Fin 4000
16 GOG Fin 2000
17 LILY Fin 3500
18 Cathy Fin 2200
19 Paul Fin 10800
20 Ben Fin 4500

How will i get Maximum and Minimum 3 salaries of EACH DEPARTMENT
ie i want to know who are the employees who are drawing top 3 max and min salaries in each department?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-13 : 03:31:51

Are you using SQL Server 2000 or SQL Server 2005?

E 12°55'05.25"
N 56°04'39.16"

archanasql
Starting Member

27 Posts

Posted - 2008-03-13 : 10:57:30

sql server 2005

X002548
Not Just a Number

15586 Posts

Posted - 2008-03-13 : 11:05:35

SELECT * FROM (
SELECT TOP 3 DEPT, SAL
FROM TABLE
ORDER BY SAL) AS A
UNION ALL
SELECT * FROM (
SELECT TOP 3 DEPT, SAL
FROM TABLE
ORDER BY SAL DESC) AS B

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-03-13 : 11:14:22

quote:
Originally posted by X002548

SELECT * FROM (
SELECT TOP 3 DEPT, SAL
FROM TABLE
ORDER BY SAL) AS A
UNION ALL
SELECT * FROM (
SELECT TOP 3 DEPT, SAL
FROM TABLE
ORDER BY SAL DESC) AS B

Brett:

quote:

How will i get Maximum and Minimum 3 salaries of EACH DEPARTMENT

- Jeff
http://weblogs.sqlteam.com/JeffS

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-13 : 11:42:13

using ROW_NUMBER function:-

;
With Sal_CTE (Low,High,EmpNo,EmpName,Dept,Sal) AS
(SELECT ROW_NUMBER() OVER(PARTITION BY dept ORDER BY sal) AS Low,
        ROW_NUMBER() OVER(PARTITION BY dept ORDER BY sal DESC) AS High,
empno,empname,dept,sal
FROM YourTable
)


SELECT t.dept,
LEFT(ls.lowsal,LEN(ls.lowsal)-1) AS 'least sal employees',
LEFT(hs.highsal,LEN(hs.highsal)-1) AS 'max sal employees'
FROM (SELECT DISTINCT dept FROM YourTable)t
CROSS APPLY (SELECT EmpName+',' AS [text()]
             FROM Sal_CTE
             WHERE Dept=t.dept
             AND High<=3
             ORDER BY High
             FOR XML PATH(''))hs(highsal)
CROSS APPLY (SELECT EmpName+',' AS [text()]
             FROM Sal_CTE
             WHERE Dept=t.dept
             AND Low<=3
             ORDER BY Low
             FOR XML PATH(''))ls(lowsal)


output
---------------------
dept  least sal employees   max sal employees
----- -----------------------------------------
Fin   GOG,Ben,Cathy         Paul,Ben,HGI
HR    ABC,John,Carly        DEF,mike,Patty
IT    Paul,GOG,Cathy        Ben,HGI,LILY

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-13 : 11:54:38

Yikes.
Keep the CTE definition and simply write

select * from sal_cte
where low <= 3 or high <= 3

E 12°55'05.25"
N 56°04'39.16"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-03-13 : 11:56:53

What's with all the cross apply and concatenation? We should be returning data from our database, not strings.


select * 
from
(
  select Dept, 'High' as Type, 
       row_number() over (partition by dept order by sal DESC) as Rank, sal, EmpName
  from Yourtable

  union all

  select Dept, 'Low' as Type, 
       row_number() over (partition by dept order by sal ASC) as Rank, sal, EmpName
  from Yourtable
) 
 x
where
  x.rank <=3
order by Dept, Type, Rank

You can use a CTE instead as well. ..

- Jeff
http://weblogs.sqlteam.com/JeffS

archanasql
Starting Member

27 Posts

Posted - 2008-03-13 : 12:12:43

Thanks for your reply.
Can we do it as a subquery?
ie.Using distinct option or Grouping by dept ?

archanasql
Starting Member

27 Posts

Posted - 2008-03-13 : 12:16:22

Hi Visakh,

thanks for your reply. I want the salaries too along with names of employees.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-03-13 : 12:28:24

archanasql -- did you miss my reply?

- Jeff
http://weblogs.sqlteam.com/JeffS

archanasql
Starting Member

27 Posts

Posted - 2008-03-13 : 12:33:42

Sorry Jeff,

I missed your reply..Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-13 : 12:54:29

quote:
Originally posted by jsmith8858

What's with all the cross apply and concatenation? We should be returning data from our database, not strings.

select *
from
(
select Dept, 'High' as Type,
row_number() over (partition by dept order by sal DESC) as Rank, sal, EmpName
from Yourtable

union all

select Dept, 'Low' as Type,
row_number() over (partition by dept order by sal ASC) as Rank, sal, EmpName
from Yourtable
)
x
where
x.rank <=3
order by Dept, Type, Rank

You can use a CTE instead as well. ..

- Jeff
http://weblogs.sqlteam.com/JeffS

just gave another format of output. Can take it directly from db if you want to.

archanasql
Starting Member

27 Posts

Posted - 2008-03-13 : 14:02:15

what is the functionality of OVER in
over (partition by dept,,,,,)
in the above query?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-14 : 02:10:21

Read Books Online about Windowed Functions.

ROW_NUMBER(), DENSE_RANK(), RANK(), NTILE() to mention a few.

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-03-14 : 06:11:34

Also see what you can do with row_number()
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/multipurpose-row-number-function.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