Execute the following to find the median of a randomly generated set of numbers:

USE AdventureWorks;

-- drop table #NumberList
create table #NumberList(NumberListID int identity(1,1),
X bigint not null)
go
set nocount on
declare @i int
set @i = 0
while(@i < 2001) begin
insert into #NumberList (X)
values (cast(rand() * 8589934591 as bigint))
set @i = @i + 1
end
go

with cteAvg as (
select [avg] = avg(X),
[min] = min(X),
[max] = max(X),
[stddev] = stdev(X)
from #NumberList ),

cteMedian as (
select X,
row_number() over (order by X asc) as high,
row_number() over (order by X desc) as low
from #NumberList)

select [avg] as average,
X as median,
[min] as minimum,
[max] as maximum,
[stddev] as 'standard deviation'
from cteMedian
cross join cteAvg
where high in (low, low+1, low-1)
go