average for 3 greatest values in a row

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 2005 Forums

Transact-SQL (2005)

average for 3 greatest values in a row

Author

Topic

namshi
Starting Member

1 Post

Posted - 2009-05-26 : 04:55:46

Hi
I have a row containing 4 columns. I need to calculate the average of the 3 greatest values.

for example:
col1 col2 col3 col4 col5
5----9---7---12---calculation
I would like col5 to retreive: 9.33

In Oracle its simple: I eliminate the lowest one and then calculate: ((col1+col2+col3+col4)-least(col1,col2,col3,col4))/3
but in SQL SERVER I didnt find a built in function like "least" or "greatest".

some suggestions?
thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-26 : 05:10:25

[code]DECLARE @Sample TABLE
(
Col1 INT,
Col2 INT,
Col3 INT,
Col4 INT,
Col5 FLOAT
)

INSERT @Sample
SELECT 25, 29, 27, 212, NULL UNION ALL
SELECT 15, 19, 17, 112, NULL

UPDATE @Sample
SET Col5 = 1.0E * (Col1 + Col2 + Col3 + Col4) / (Col1 + Col2 + Col3 + Col4 - (SELECT MIN(u) FROM (SELECT Col1 AS u UNION SELECT Col2 UNION SELECT Col3 UNION SELECT Col4) AS d))

SELECT *
FROM @Sample[/code]

E 12°55'05.63"
N 56°04'39.26"

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-05-26 : 05:14:09

try this one too


declare @temp table(id int identity(1,1),col1 int, col2 int, col3 int, col4 int, col5 int)

insert into @temp select 5,9,7,12,null
insert into @temp select 22,9,7,12,null

select id,col,colval into #temp 
from @temp
unpivot (colval for  col in (col1,col2,col3,col4))p

select id,avg(cast(colval as numeric(18,3))) from  #temp where colval not in ( select min(colval) from #temp)
group by id

drop table #temp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-26 : 05:17:49

[code]UPDATE @Sample
SET Col5 = (Col1 + Col2 + Col3 + Col4 - (SELECT MIN(u) FROM (SELECT Col1 AS u UNION SELECT Col2 UNION SELECT Col3 UNION SELECT Col4) AS d)) / 3.0E[/code]

E 12°55'05.63"
N 56°04'39.26"

nr
SQLTeam MVY

12543 Posts

Posted - 2009-05-26 : 05:27:09

A slight change to blkr's suggestion

declare @temp table(id int identity(1,1),col1 int, col2 int, col3 int, col4 int, col5 int)

insert into @temp select 5,9,7,12,null
insert into @temp select 22,9,7,12,null

select id, AVG(colval*1.0)
from
(select id,col,colval, z_seq = ROW_NUMBER() over(partition by id order by colval)
from @temp
unpivot (colval for col in (col1,col2,col3,col4))p
) a
where z_seq <= 3
group by id

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources