select
    avg(a.value) as median
from
    (
        select 
            value, 
            row_number() over (order by value asc)
        from 
            test_row_count_and_rank
    ) a,
    (
        select 
            value, 
            row_number() over (order by value desc)
        from 
            test_row_count_and_rank
    ) b
where
    a.value = b.value and
    abs(a.row_number - b.row_number) <= 1;