Better Median??

I recently had to use my own little median technique again on a report here at work, and had posted it before, but wasn't sure if anyone had seen it. I have read Celko's and others techniques for generating a median and haven't seen one more efficent.

Does anyone have a better way they can think of? I think this bad boy is pretty short & efficient.

First, if you want to return the middle number or the higher one next to the middle if there is an even number:

SELECT x.Value AS median
FROM Vals x
CROSS JOIN Vals y
GROUP BY x.Value
HAVING SUM(SIGN(x.Value-y.Value)) IN (1,0)

Change the " IN (1,0)" to "IN (-1,0)" to get the lower value if there is an even # of values.

Basically, we are saying compare each number to all possible numbers, and add up values of 1,0 or -1 depending if the first number is less, equal or higher than the second. The number that returns 0 is right in the middle ... If there is no middle, a -1 or 1 is returned. There will never be a 0 and (-1 or 1) at the same time returned.

To get the financial median (avg of the 2 values middle values if there is an even number), you need to encapsulate the results of the above into a subquery, allow for not just (-1,0) but all three (-1,0,1) and then take the AVG of the values returned.

That is,

SELECT Avg(Median) as Median FROM
(
SELECT x.Value AS median
FROM Vals x
CROSS JOIN Vals y
GROUP BY x.Value
HAVING SUM(SIGN(x.Value-y.Value)) IN (1,0,-1)
) A

If there is an even number of values, the lower and higher middle ones are averaged. If there is an odd number, only the middle value is returned and averaged (which of course has no effect).

Most other techniques used several COUNT(*) subqueries which this one avoids.

Critique and enjoy!

- Jeff