Median Value

[M]

A table has 100 records, field1 is numerical.field
I want get the median value.
Are there a easy way to get the median value
besides sort the table and loop the recrods until get the 50th record

Thanks

[Aaron Bertrand - MVP]

http://www.aspfaq.com/2506

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/

"M" <m...@hotvoice.com> wrote in message
news:uUWkdyX9...@tk2msftngp13.phx.gbl...

[Anith Sen]

Here is a standard approach:

SELECT col
FROM ( SELECT t1.col, COUNT( * )/2.,
SUM( CASE WHEN t2.col <= t1.col
THEN 1 ELSE 0 END ),
SUM( CASE WHEN t2.col >= t1.col
THEN 1 ELSE 0 END )
FROM tbl t1
CROSS JOIN tbl t2
GROUP BY t1.col ) D ( col, Total, lpart, rpart )
WHERE lpart >= col
AND rpart -1 >= col ;

--
Anith