One small observation from trying this: you have to be sure that the ORDER BY fields are unique, otherwise the Ascending and Descending sequences may not mirror each other, so the sum of the UP and DOWN row_number() fields is not guaranteed to be the total row count.

With that in mind, though, the central idea here does give another way to calculate a MEDIAN - sort for row_number on the value you are interested in (possibly including an ID in the ORDER BY to separate duplicate values) , then your median is the value where UP and DOWN are the same, or , if there is an even number of values, it's the average of those two where abs(UP - DOWN) = 1

Both these cases reduce to

avg(value).....WHERE (UP - DOWN) between -1 and 1

This snippet finds the median of the field enSum, in the table called Enrolments:

with RankedEnrol

As

(

Select enSum

, row_number() over (ORDER BY enSum, enID) UP-- identity is included in the ORDER BY to ensure uniqueness

, row_number() over (ORDER BY enSum DESC, enID DESC) DOWN

FROM Enrolments

)

SELECT avg(enSum) MedianEnrol

from RankedEnrol

WHERE (UP - DOWN) between -1 and 1