Top 5 percent - 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)

Top 5 percent

Author

Topic

Scott
Posting Yak Master

145 Posts

Posted - 2004-09-16 : 08:48:08

I have a table:


Create table Data (
    Value1 decimal(9,2),
    Value2 decimal(9,2),
    Value3 decimal(9,2),
    Value4 decimal(9,2),
    Value5 decimal(9,2)
)

I am wanting to return the average of the top 5 for each value. This is how I have been doing it:


Create table #Data (
    ID int,
    avgValue1 decimal(9,2),
    avgValue2 decimal(9,2),
    avgValue3 decimal(9,2),
    avgValue4 decimal(9,2),
    avgValue5 decimal(9,2)
)
Insert into #Data (ID) values (1)

Update #Data set avgValue1 = 
	(Select Avg(Value1) from 
	  (select top 5 percent Value1 from Data 
		order by Value1 desc) a)
  where ID = 1

Update #Data set avgValue2 = 
	(Select Avg(Value2) from 
	  (select top 5 percent Value2 from Data 
		order by Value2 desc) a)
 where ID = 1

... for each value

Is there a better way to do this so that I don't have to average each value individually with it's own order by clause?

Thanks
Scott

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-09-16 : 09:29:18

A bit ugly,
but 1 statement none the less:

[CODE]
Create table #Data (
Value1 decimal(9,2),
Value2 decimal(9,2),
Value3 decimal(9,2),
Value4 decimal(9,2),
Value5 decimal(9,2)
)
go
insert into #data select datepart(ms, getdate()) * rand(), datepart(ms, getdate()) * rand(), datepart(ms, getdate()) * rand(), datepart(ms, getdate()) * rand(), datepart(ms, getdate()) * rand() GO
insert into #data select datepart(ms, getdate()) * rand(), datepart(ms, getdate()) * rand(), datepart(ms, getdate()) * rand(), datepart(ms, getdate()) * rand(), datepart(ms, getdate()) * rand() GO
insert into #data select datepart(ms, getdate()) * rand(), datepart(ms, getdate()) * rand(), datepart(ms, getdate()) * rand(), datepart(ms, getdate()) * rand(), datepart(ms, getdate()) * rand() GO
insert into #data select datepart(ms, getdate()) * rand(), datepart(ms, getdate()) * rand(), datepart(ms, getdate()) * rand(), datepart(ms, getdate()) * rand(), datepart(ms, getdate()) * rand() GO
insert into #data select datepart(ms, getdate()) * rand(), datepart(ms, getdate()) * rand(), datepart(ms, getdate()) * rand(), datepart(ms, getdate()) * rand(), datepart(ms, getdate()) * rand() GO
insert into #data select datepart(ms, getdate()) * rand(), datepart(ms, getdate()) * rand(), datepart(ms, getdate()) * rand(), datepart(ms, getdate()) * rand(), datepart(ms, getdate()) * rand() GO
insert into #data select datepart(ms, getdate()) * rand(), datepart(ms, getdate()) * rand(), datepart(ms, getdate()) * rand(), datepart(ms, getdate()) * rand(), datepart(ms, getdate()) * rand() GO
insert into #data select datepart(ms, getdate()) * rand(), datepart(ms, getdate()) * rand(), datepart(ms, getdate()) * rand(), datepart(ms, getdate()) * rand(), datepart(ms, getdate()) * rand() GO
insert into #data select datepart(ms, getdate()) * rand(), datepart(ms, getdate()) * rand(), datepart(ms, getdate()) * rand(), datepart(ms, getdate()) * rand(), datepart(ms, getdate()) * rand() GO
insert into #data select datepart(ms, getdate()) * rand(), datepart(ms, getdate()) * rand(), datepart(ms, getdate()) * rand(), datepart(ms, getdate()) * rand(), datepart(ms, getdate()) * rand() GO
insert into #data select datepart(ms, getdate()) * rand(), datepart(ms, getdate()) * rand(), datepart(ms, getdate()) * rand(), datepart(ms, getdate()) * rand(), datepart(ms, getdate()) * rand() GO
insert into #data select datepart(ms, getdate()) * rand(), datepart(ms, getdate()) * rand(), datepart(ms, getdate()) * rand(), datepart(ms, getdate()) * rand(), datepart(ms, getdate()) * rand() GO
insert into #data select datepart(ms, getdate()) * rand(), datepart(ms, getdate()) * rand(), datepart(ms, getdate()) * rand(), datepart(ms, getdate()) * rand(), datepart(ms, getdate()) * rand() GO
insert into #data select datepart(ms, getdate()) * rand(), datepart(ms, getdate()) * rand(), datepart(ms, getdate()) * rand(), datepart(ms, getdate()) * rand(), datepart(ms, getdate()) * rand() GO
insert into #data select datepart(ms, getdate()) * rand(), datepart(ms, getdate()) * rand(), datepart(ms, getdate()) * rand(), datepart(ms, getdate()) * rand(), datepart(ms, getdate()) * rand() GO

--**** the select starts here:

select v1.Value1, v2.Value2, v3.Value3, v4.Value4, v5.Value5
FROM (select top 5 percent 1 as tempid, avg(Value1) as value1 from #Data order by Value1 desc) v1
JOIN (select top 5 percent 1 as tempid, avg(Value2) as value2 from #Data order by Value2 desc) v2
on v1.tempid = v2.tempid
JOIN (select top 5 percent 1 as tempid, avg(Value3) as value3 from #Data order by Value3 desc) v3
on v1.tempid = v3.tempid
JOIN (select top 5 percent 1 as tempid, avg(Value4) as value4 from #Data order by Value4 desc) v4
on v1.tempid = v4.tempid
JOIN (select top 5 percent 1 as tempid, avg(Value5) as value5 from #Data order by Value5 desc) v5
on v1.tempid = v5.tempid

[/CODE]

Duane.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-09-16 : 09:52:17

What's with the ID ?
Anyway:

select
	--identity(int,1,1) as ID,
	(Select Avg(Value1) from(
		select top 5 percent Value1 from Data order by Value1 desc) as v1) as avgValue1
	,(Select Avg(Value2) from(
		select top 5 percent Value2 from Data order by Value2 desc) as v2) as avgValue2
	,(Select Avg(Value3) from(
		select top 5 percent Value3 from Data order by Value3 desc) as v3) as avgValue3
	,(Select Avg(Value4) from(
		select top 5 percent Value4 from Data order by Value4 desc) as v4) as avgValue4
	,(Select Avg(Value6) from(
		select top 5 percent Value5 from Data order by Value5 desc) as v5) as avgValue5
--into #Data

rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-09-16 : 10:03:07

Thanks, for the test data ditch!

I don't think your query returns the avg of the top 5 percent.
It returns the avg of the whole kazooba

Like your join construct though

rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-09-16 : 10:04:07

OK!
Thanks 4 testing it for me :)

Duane.

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-09-16 : 10:19:39

Yip. I posted the wrong piece of code.

Should have been this.

[CODE]
select avg(v1.Value1), avg(v2.Value2), avg(v3.Value3), avg(v4.Value4), avg(v5.Value5)
FROM (select top 5 percent 1 as tempid, Value1 as value1 from #Data order by Value1 desc) v1
JOIN (select top 5 percent 1 as tempid, Value2 as value2 from #Data order by Value2 desc) v2
on v1.tempid = v2.tempid
JOIN (select top 5 percent 1 as tempid, Value3 as value3 from #Data order by Value3 desc) v3
on v1.tempid = v3.tempid
JOIN (select top 5 percent 1 as tempid, Value4 as value4 from #Data order by Value4 desc) v4
on v1.tempid = v4.tempid
JOIN (select top 5 percent 1 as tempid, Value5 as value5 from #Data order by Value5 desc) v5
on v1.tempid = v5.tempid
[/CODE]

I still like the Stone Antelopes solution more though :)

Duane.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-09-16 : 10:33:38

Stone Antelopes ?
Whats that ?
You had 1 select from 5 derived tables
I had 1 select from 5 subselects from 5 derived tables
:)

rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-09-16 : 10:38:37

I was praising your solution - is a moose an antelope? we don't have them here - but I made that assumption.

Duane.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-09-16 : 10:46:31

Sorry, I am abit slow today it appears :-)
But Thanx! - the moose rocks !
A moose would be akin to your antelopes, yes it would.

PS.I liked your solution as well :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources