Parin Parin - 1 year ago 111
MySQL Question

Finding median in mysql by using row num in Mysql

I want to find the median(consider lower half value for even count) of Salary values in Mysql. Table consists of a ID, Salary.

Since there is no rownum, I wrote the following code to generate rownum in Mysql.

set @r = 0;
Select rownum, id, Salary from
(Select @r:=@r+1 as rownum, id, Salary from E
order by Salary asc) p
where p.rownum = (Select max(p.rownum) from p);

This won't work because most internal query runs first. Can anyone has a solution to this?

Answer Source

Actually, because MySQL processes the sub query before the outer query, you can do:

Where p.rownum * 2 in (@r, @r + 1)

Note: your question doesn't calculate the median. This is one method.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download