median in mySQL

Question

I try to find the median value of l from the table chair:

set @r = (select CEIL(COUNT(*)/2) from chair);
select max(l) from chair order by lat_n limit @r;

Is anyone know why I get this error:

ERROR 1064 (42000) at line 2: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@r' at line 1

Does this answer your question? [Using variable in a LIMIT clause in MySQL](https://stackoverflow.com/questions/245180/using-variable-in-a-limit-clause-in-mysql) — HoldOffHunger, Oct 22 '20 at 16:04
LIMIT with dynamic parameter(s) may be used in prepared statement only, not in regular query. — Akina, Oct 22 '20 at 16:21
For MySQL 8+ use [NTH_VALUE()](https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html#function_nth-value) function. — Akina, Oct 22 '20 at 16:23

score 0 · Answer 1 · answered Oct 22 '20 at 15:58

I woudl suggest window functions. For instance, if l is numeric:

select (case when cnt % 1 = 1
             then max(case when tile = 1 then l end)
             else (max(case when tile = 1 then l end) + min(case when tile = 2 then l end)) / 2
        end)
from (select c.*, count(*) over () as cnt,
             ntile(2) over (order by l) as tile
      from chair c
     ) c

median in mySQL

1 Answers1