Alan.B (7/31/2013)

---

TheSQLGuru (7/30/2013)

---

Fortunately for you SQL 2012 has much better Windowing Function support. Look into the PERCENTILE_CONT function. I also HIGHLY recommend you purchase Itzik Ben-Gan's SQL Server 2012 High-Performance TSQL Using Window Functions book.

Just an interesting FYI...

I have never used PERCENTILE_CONT but I do have Server 2012 High-Performance TSQL Using Window Functions. With the same sample data from earlier (#T), this is a modified version of Ben-Gan's 2012-based solution using PERCENTILE_COUNT:

WITH t(r,m) AS

(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),

PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY i) OVER(PARTITION BY NULL)

FROM #T

)

SELECT m AS median

FROM t

WHERE r=1;

Though it is much simpler, Ben-Gan's 2012-PERCENTILE_CONT solution is about twice as slow as any other solution posted thus far. Again, just an FYI.