0

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;`
GMB
  • 216,147
  • 25
  • 84
  • 135
Hsin
  • 3
  • 1

1 Answers1

0

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.

GMB
  • 216,147
  • 25
  • 84
  • 135
  • Why it is safer to make one more level of nesting? Would you please share a little bit more about that? >> from (select s.* from station order by lat_n) s – Hsin Mar 29 '20 at 00:43
  • @Hsin: variables and ordering are tricky; it is safer to order the resultset in a subquery, then use variables. – GMB Mar 29 '20 at 08:59
  • Roger that. Thanks!! – Hsin Mar 29 '20 at 11:44