Wednesday, August 08, 2012 4:22 PMcreate 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
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.