When I have discovered MySQL User-defined variables at the first time, I didn’t pay much attention to them and didn’t realize their strength. At first, one can think that they are just one of the many unnoticed MySQL features. Well, I was wrong. Here, I will try to summarize their strength, though I think there is […]

I just read SQL: Ranking without self join, in which Shlomi Noach shares a nice MySQL-specific trick based on user-defined variables to compute rankings.

Shlomi's trick reminds me somewhat of the trick I came across little over a year ago to caclulate percentiles. At that time, several people pointed out to me too that using user-defined variables in this way can be unreliable.The problem with user-defined variablesSo what is the problem exaclty? Well, whenever a query assigns to a variable, and that same variable is read in another part of the query, you're on thin ice. That's because the …

After stepping off of the GROUP_CONCAT() solution for calculating quantiles I figured it would be nice to find a better way to calculate the median too.
Solution
I previously wrote on how to calculate the median using GROUP_CONCAT(), but I think that this is a better way:

SELECT     AVG(length) AS median                 -- take the average of left and right median
,          MIN(length) AS left_median            -- 
,          MAX(length) AS right_median           --
,          @l          AS left_median_position   --
,          @r          AS right_median_position  --
FROM (
     SELECT     @n, length                       -- @n is just here to …

My previous post on calculating percentiles with MySQL generated some comments and good discussion. In particular, I got some very interesting comments from Vladimir.

Basically, Vladimir was doubtful whether the GROUP_CONCAT() solution would be optimal in comparison to a JOIN. His proposal is to solve it like this:

SELECT   SUM(g1.r) sr
,        g2.length l
,        SUM(g1.r)/(SELECT COUNT(*) FROM film) p
FROM    (SELECT COUNT(*) r, length FROM film GROUP BY length) g1
JOIN    (SELECT COUNT(*) r, length FROM film GROUP BY length) g2
ON       g1.length < g2.length
GROUP BY g2.length
HAVING p > 0.9
ORDER BY p
LIMIT 1

First, this query sets up two identical subqueries in …