WITH PERCENTILE_CTE AS ( SELECT LAT_N, PERCENT_RANK() OVER (ORDER BY LAT_N) AS PERCENTILE FROM STATION )

SELECT ROUND(LAT_N, 4) AS MEDIAN_ROUNDED FROM PERCENTILE_CTE where PERCENTILE = 0.5

SET NOCOUNT ON;

SELECT TOP 1 CAST(ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY LAT_N) OVER(),4) AS DECIMAL(10,4)) FROM STATION; go

SELECT ROUND(AVG(LAT_N), 4) FROM ( SELECT LAT_N, ROW_NUMBER() OVER (ORDER BY LAT_N) AS row_num, COUNT(*) OVER () AS total_count FROM STATION ) AS temp WHERE (total_count % 2 = 0 AND row_num IN (total_count/2)) OR (total_count % 2 = 1 AND row_num = (total_count + 1)/2);

This is my solution:

WITH cte as (SELECT LAT_N, ROW_NUMBER() OVER(ORDER BY LAT_N asc) as rn_asc, ROW_NUMBER() OVER(ORDER BY LAT_N desc) as rn_desc FROM station)

SELECT ROUND(AVG(LAT_N),4) FROM cte WHERE ABS(CAST(rn_asc AS SIGNED) - CAST(rn_desc AS SIGNED)) <= 1;

WITH RN AS (SELECT ROW_NUMBER() OVER(ORDER BY LAT_N) AS ROW_N, LAT_N FROM STATION),

MED AS (SELECT COUNT(*) AS TOTAL FROM RN) SELECT ROUND( CASE WHEN MOD(M.TOTAL,2)<>0 THEN (SELECT LAT_N FROM RN WHERE ROW_N=(SELECT (M.TOTAL+1)*0.5))[](https://) ELSE ( (SELECT LAT_N FROM RN WHERE ROW_N=(SELECT M.TOTAL*0.5))+ (SELECT LAT_N FROM RN WHERE ROW_N=(SELECT (M.TOTAL*0.5)+1)) )/2 END ,4) AS MEDIAN FROM MED M