Thanks for those links.

I've had a bookmark for some time to Ben-Gan's original write-up, which you might want to add to your list of median computation web pages:

http://www.sqlmag.com/article/tsql3/calculating-the-median-gets-simpler-in-sql-server-2005

That article explains how/why the method works. I found the logic confusing at first until I absorbed the fact that integer division in T-SQL truncates remainders. Here are my notes I created for myself after running Ben-Gan's method:

/*

HOW IT WORKS (Returns median of [Value]), relying on INTeger division truncating remainders:

The CTE's ROW_NUMBER function orders the data in sequence of [Value], partitioned here by EmployeeID, thought that's not required.

THE CTE also includes a Count() function for the number of rows per EmployeeID.

Then the main query selects AVG([Value]) as the median, from a list of 2 rows specified by WHERE:

-RowNum = (Cnt + 1)/2

-RowNum = (Cnt + 2)/2

Consider the 2 cases possible - an even or odd number for Cnt:

EVEN (e.g., Cnt=16)

(Cnt + 1)/2 = 8! b/c Cnt is by default an INT and SQL's INT division truncates the remainder of 17/2

(Cnt + 2)/2 = 9.

So the average of the 8th and 9th rows is returned as the median.

ODD (e.g., Cnt=15)

(Cnt + 1)/2 = 8

(Cnt + 2)/2 = 8 (again, INTeger math truncation).

The average of the 8th and 8th rows is returned as the median.

*/

Rich