calculating median

calculating median

• Wednesday, August 08, 2012 4:22 PM

create table t (
i int ,
c1 int ,
c2 int ,
c3 int ,
c4 int ,
c5 int ,
c6 int ) ;

the requirement is as follows

a) c4 is the median of c1 , c2 , c3
b) c5 is the median  of c2 , c3 , c4
c) c6 is the median of c3 , c4 , c5

What is the best way to accompolish . This tables 2.3 million records.

I am on Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0

All Replies

• Wednesday, August 08, 2012 6:24 PM

Steve Kass posted the following approach, based on the Northwind database.

```create function dbo.Median (
@lo int, @hi int, @rank int
) returns int as begin
if @rank = 0 select
@lo = min(orderid),
@hi = max(orderid),
@rank = (1 + count(*))/2
from Northwind..[order details]
if @lo >= @hi
return @lo
declare @mid int
declare @leftct int
select
@mid = max(orderid),
@leftct = count(*)
from Northwind..[order details]
where orderid >= @lo
and orderid <= (@lo + @hi)/2
if @leftct < @rank
return dbo.Median(@mid+1, @hi, @rank - @leftct)
if @leftct > @rank
return dbo.Median(@lo, @mid, @rank)
return @mid
end
go
select dbo.Median(0,0,0)
go
drop function Median
go```

He also has an earlier, more procedural post, that you can look at in the same thread.

RLF