50% OFF!!!

Booking.com

Friday, June 19, 2015

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...