CODEWITHSUNDEEP
mysql
Raj
Raj

Reputation: 163

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 

Table Structure

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?

Upvotes: 1

Views: 106

Answers (3)

Ramkinkar
Ramkinkar

Reputation: 1

Since there is no built-in function for calculating median in MySQL while solving a hacker rank problem to find out the median, I was really got stuck, while searching and understanding the concept of median, I got to this easy method to calculate the median in MySQL

   with cal_median as (
select*,
    row_number() over(order by column_name ) as rn,
    count(*) over() as rc
    from table_name
) 
select column_name from cal_median 
where rn in((rc+1)/2 ,(rc+2)/2);

  • This is valid for both odd set of values and even set of values

  • In case if you are using Oracle just use the built-in function

    select median(colum_name) from table_name

Upvotes: 0

sagi
sagi

Reputation: 40481

You can't use a derived table that was created in the outer query, in a sub query.

You can create the table and use it:

CREATE TABLE TMP_FOR_SELECT AS
Select @r:=@r+1 as rownum, id, Salary from E
order by Salary asc;

Select rownum, id, Salary
from TMP_FOR_SELECT p
where p.rownum = (Select max(t.rownum) from TMP_FOR_SELECT t);

EDIT: For the median salary, try this:

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 = ROUND((Select COUNT(*) from e)/2);

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269773

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.

Upvotes: 2

Related Questions