MySQL | find median (Nth element) without join!

This is my suggestion to find on mysql select command

the Median (Nth element) without joining the table to itself (or to new custom one).

My method, uses string manipulation, 

so I'm not sure it is good for big tables.

(I tested it with medium size tables on Mysql 5.5.28)

The advantage of this method, 

that it works also if we need to find the MEDIAN for each group in the select query.

the code is using: SUBSTRING_INDEX and GROUP_CONCAT methods.


Here is test code for test table:

DROP TABLE test.test_median
CREATE TABLE test.test_median AS
SELECT 'book' AS grp, 4 AS val UNION ALL
SELECT 'book', 7 UNION ALL
SELECT 'book', 2 UNION ALL
SELECT 'book', 2 UNION ALL
SELECT 'book', 9 UNION ALL
SELECT 'book', 8 UNION ALL
SELECT 'book', 3 UNION ALL

SELECT 'note', 11 UNION ALL

SELECT 'bike', 22 UNION ALL
SELECT 'bike', 26 

and the code for finding the median for each group:

SELECT grp,
         SUBSTRING_INDEX( SUBSTRING_INDEX( GROUP_CONCAT(val ORDER BY val), ',', COUNT(*)/2 ), ',', -1) as the_median,
         GROUP_CONCAT(val ORDER BY val) as all_vals_for_debug
FROM test.test_median
GROUP BY grp

The output:

grp	the_median	all_vals_for_debug
bike	22	22,26
book	4	2,2,3,4,7,8,9
note	11	11

Hope it helps you there...