You can find a survey and performance comparison of the main SQL Server methods in Aaron Bertrand's articles Best approaches for grouped median and What is the fastest way to calculate the median?

With suitable indexing, the method proposed by Peter Larsson often performs best, though the logic is a little trickier to follow. The following example is quoted from Aaron's first article, using a sales data set:

SELECT d.SalesPerson, w.Median
FROM
(
 SELECT SalesPerson, COUNT(*) AS y
 FROM dbo.Sales
 GROUP BY SalesPerson
) AS d
CROSS APPLY
(
 SELECT AVG(0E + Amount)
 FROM
 (
   SELECT z.Amount
    FROM dbo.Sales AS z
    WHERE z.SalesPerson = d.SalesPerson
    ORDER BY z.Amount
    OFFSET (d.y - 1) / 2 ROWS
    FETCH NEXT 2 - d.y % 2 ROWS ONLY
 ) AS f
) AS w(Median);

You might also be interested in reading my article about Calculating the Median with a Dynamic Cursor, which performs surprisingly well. I also have Improving the Row Numbering Median Solution showing how to improve the performance of Peter's solution further.

You can find a survey and performance comparison of the main SQL Server methods in Aaron Bertrand's articles Best approaches for grouped median and What is the fastest way to calculate the median?

With suitable indexing, the method proposed by Peter Larsson often performs best, though the logic is a little trickier to follow. The following example is quoted from Aaron's first article, using a sales data set:

SELECT d.SalesPerson, w.Median
FROM
(
 SELECT SalesPerson, COUNT(*) AS y
 FROM dbo.Sales
 GROUP BY SalesPerson
) AS d
CROSS APPLY
(
 SELECT AVG(0E + Amount)
 FROM
 (
   SELECT z.Amount
    FROM dbo.Sales AS z
    WHERE z.SalesPerson = d.SalesPerson
    ORDER BY z.Amount
    OFFSET (d.y - 1) / 2 ROWS
    FETCH NEXT 2 - d.y % 2 ROWS ONLY
 ) AS f
) AS w(Median);

You might also be interested in reading my article about Calculating the Median with a Dynamic Cursor, which performs surprisingly well. I also have Improving the Row Numbering Median Solution showing how to improve the performance of Peter's solution further.

Related Q & A here:

• Using Percentile_Disc to compute Median/Interquartiles