1

I have a table with a number column, now i want to select the middle value from the column after sorting.

demo table

index   lat_n      city
  1    223.0000   hyderabad
  2    153.7890   chennai
  3    257.8900   mumbai

now my out put should be

223.0000

if possible i would like to know how to do this in mysql too.

Santhosh Reddy
  • 123
  • 1
  • 6
  • Could you please explain in details. `ROWNUM` is the value generated in the order you select the records. Filtering the values using `ROWNUM` from the value in another table is not relevant – Jim Macaulay Aug 12 '20 at 13:31
  • i am trying to get index of middle value of sorted column , so that i get the proper output, i am new to sql, my query might be wrong, i will edit my question – Santhosh Reddy Aug 12 '20 at 13:35

2 Answers2

5

You can get the middle value using median

with rws as (
  select 153.789 l from dual union all
  select 223 l from dual union all
  select 257.89 l from dual 
)
  select median ( l ) from rws;
  
MEDIAN(L)   
         223

If you have an even number of rows, this will return the value that's halfway between the two middle values

Chris Saxon
  • 9,105
  • 1
  • 26
  • 42
1

You have to be careful with rownum, as you have to order the results before you try to evaluate that, and you can't use it to compare with equality except for = 1. You may find this interesting.

You could use analytic functions to get the row number and overall row count:

select lat_n, city, row_number() over (order by lat_n) as rn, count(*) over () as cnt
from station

and then use that in a subquery (inline view or CTE) to filter:

select lat_n, city
from (
  select lat_n, city, row_number() over (order by lat_n) as rn, count(*) over () as cnt
  from station
)
where rn = ceil(cnt/2)

You've said the column is a number, but with the trailing zeros it looks like you might be storing it as a string; in which case you may need to convert to a number before ordering.

Alex Poole
  • 183,384
  • 11
  • 179
  • 318
  • thank you for you answer , i now understood how to use the row number , but i took that answer as a built function is available. – Santhosh Reddy Aug 12 '20 at 13:46
  • As long as you won't ever have an even number of rows - if that's true then I agree *8-) Or maybe you do want to interpolate if that happens. That wasn't clear from your question though. – Alex Poole Aug 12 '20 at 13:54