Trying to put a variable into limit to find a median

I trying to use mysql to solve the following solutions: https://www.hackerrank.com/challenges/weather-observation-station-20/problem

Understanding that a variable cannot be put into LIMIT statement (from this )

My approach>

to declare a new variable to record rowIDs, and use rowID to retrieve the record in the middle. However, it seems that rowID is not working well. Could anyone give me some advises?

SELECT ROUND(COUNT(LAT_N)/2,0) FROM STATION into @count;
SELECT ROUND(a.LAT_N,4) FROM (
SELECT *,@row := @row + 1 FROM STATION s, (SELECT @row := 0) r
    WHERE @row <=@count
    ORDER BY s.LAT_N ASC) a
ORDER BY a.LAT_N DESC  LIMIT 1;`

Solution

If you are running MySQL 8.0, this is simpler done with window functions:

select round(avg(lat_n), 4) median_lat_n
from (
    select s.*, row_number() over(orer by lat_n) rn
    from station s
    where lat_n is not null
) s
where rn * 2 in (rn - 1, rn, rn + 1)

In earlier versions, variables make it bit tricky; we need one more level of nesting to make it safe:

select round(avg(lat_n), 2) median_lat_n
from (
    select s.*, @rn := @rn + 1 rn
    from (select * from station order by lat_n) s
    cross join (select @rn := 0) p
) s
where rn  * 2 in (rn - 1, rn, rn + 1)

The logic is as follows: first enumerate the rows, ordered by lat_n. If the row count is uneven, we pick the middle row; if it is even, we take the average of the two middle values.