List Top 10 values -Query - SQL Server Forums

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)

List Top 10 values -Query

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-09-22 : 08:49:59

myl writes "How can I get the average of the top 10 salaries for each department in a company by a query in SQL ?"

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-22 : 08:56:50

First get a list of top 10 salaries for each deptartment


Select deptId, Salary
From payroll A
Where (Select count(*) From payroll Where deptId = A.deptId and Salary > A.Salary) < 10

then average them...


Select deptId, AverageSalary = avg(Salary)
From payroll A
Where (Select count(*) From payroll Where deptId = A.deptId and Salary > A.Salary) < 10
Group By deptId

--although you may have to do:

Select deptId, AverageSalary = avg(Salary)
(
Select deptId, Salary
From payroll A
Where (Select count(*) From payroll Where deptId = A.deptId and Salary > A.Salary) < 10
) B
Group By deptId

Corey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources