How do I find the median value of a column in MySQL?

There is quite a bit of discussion here on calculating median values from a MySQL table. Just search the page for 'median'.

As an aside, it strikes me as remiss that there is no built-in function to do this. Median is often more descriptive of central tendency than mean. Access/VBA has the same hole in its function list.

I haven't seen a solution anywhere that manages to get the median in a single query. I don't mind temp tables, but if they're not necessary, great! Here's what I came up with:

SELECT AVG(profit) median, nofitems FROM(
  SELECT x.profit, SUM(SIGN(1.0-SIGN(y.profit-x.profit))) diff, count(*) nofitems, floor(count(*)+1/2)
  FROM brand_prof x, brand_prof y
  GROUP BY x.profit
  HAVING SUM(SIGN(1.0-SIGN(y.profit-x.profit))) = floor((COUNT(*)+1)/2)
      OR SUM(SIGN(1.0-SIGN(y.profit-x.profit))) = ceiling((COUNT(*)+1)/2)
) x;

I tested this for an even set, and got the right answer. brand_prof is just two columns: brand_name, and profit, a decimal value. If this were integer values, you may have to cast "ceiling((CAST COUNT(*) AS DECIMAL)..." More than I've tested. The cool idea to use a cartesian product and relate the sum of the signs was not mine. I have forgotten the author, unfortunately.