calculating median

Question

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

Answer 1

https://groups.google.com/forum/?fromgroups#!topic/microsoft.public.sqlserver.programming/TzSgaOTT14w%5B1-25%5D

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