Getting the median value from the database

Question

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.

Input Format

The STATION table is described as follows:

Field : Type
ID    : NUMBER
CITY  : VARCHAR2(21)
STATE : VARCHAR2(2)
LAT_N : NUMBER
LONG_W: NUMBER

where LAT_N is the northern latitude and LONG_W is the western longitude.

I could only manage to get the row index for the median value with

select floor((count(lat_n)+1)/2) from station;

which is row index 250. The next step is to use this value to extract out the lat_n value at row index 250. How do I transform to SQL?

Answer 1

For mySQL This will work,

   SELECT ROUND(S1.LAT_N, 4) 
    FROM STATION AS S1 
    WHERE (SELECT ROUND(COUNT(S1.ID)/2) - 1 
           FROM STATION) = 
          (SELECT COUNT(S2.ID) 
           FROM STATION AS S2 
           WHERE S2.LAT_N > S1.LAT_N);

refrence : https://nikkesan.gitbook.io/hackerrank/practice-1/sql/aggregation/untitled-2