A median is defined as a number separating the higher half of a data set from the lower half. Query the median of the Northern Latitudes (LAT_N) from STATION and round your answer to decimal places. (where LAT_N is the northern latitude and LONG_W is the western longitude.) Input STATION table: enter image description here
Median is the middle number of sorted numbers. So I found the total records using COUNT function. The count was 499. So the median is (499+1)/2 = 250.
After getting the middle number as 250 I used this query:
SELECT ROUND(LAT_N,4) FROM (
SELECT LAT_N, DENSE_RANK() OVER (ORDER BY LAT_N) dens_rank
FROM STATION) AS E
WHERE E.dens_rank = 250
(Anyway Hacker Rank approved it!). But I want to pass that 250 dynamically.
Something Like:
SELECT ROUND(LAT_N,4) FROM (
SELECT LAT_N, DENSE_RANK() OVER (ORDER BY LAT_N) dens_rank
FROM STATION) AS E
WHERE E.dens_rank = COUNT((E.LAT_N+1)/2)
Error Message: ERROR 1111 (HY000) at line 1: Invalid use of group function
