Math Statistics - 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)

Math Statistics

Author

Topic

Scott
Posting Yak Master

145 Posts

Posted - 2004-09-08 : 09:31:50

I have a table:


create table test (
   Name as varchar,
   Result as decimal(9,2)
)

I am wanting to calculate the average of the top 5% results. I could do the following:


Select avg(Result) as TopResults
from
   (Select top 5% * from test order by Result Desc) a

Is there a better/quicker way?

Thanks
Scott

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-09-08 : 09:36:52

Hey Scott,
That's probably about the best way to do it.

Duane.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-08 : 09:37:44

wouldn't this work:

<edit>
i forgot to put the parent select in:

Select avg(Result) as TopResults
from (Select top 5 percent Result
from test order by Result Desc) a

it's probably faster if u select just Result instead of *
Go with the flow & have fun! Else fight the flow

Scott
Posting Yak Master

145 Posts

Posted - 2004-09-08 : 09:46:19

spirit1 yours would not work because you can't have an order by clause that is not contained in the agregate function.

I am trying to find a way that will have the least performance impact. In my solution above there is a table scan then a sort operation. Pretty big preformance hit with a few hundred thousand records. I could put an index on the result field which will help with the table scan.

Just throwing it open to see if there is a better way.

Thanks

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-08 : 10:03:37

what do you mean u can't?? u dont have it...

this works for me and it's analog to yours:
use northwind
select avg(EmployeeId) as AvgEmp
from (select top 5 percent EmployeeId from orders order by EmployeeId desc) a

Go with the flow & have fun! Else fight the flow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources