Finding median date in Oracle
Does anyone know the correct way to get the median date from an SQL query?
I have seen the median function on the Oracle site but it never refers to dates.
Ex. select median(birth_dt) from some_table;
Networks
Does anyone know the correct way to get the median date from an SQL query?
I have seen the median function on the Oracle site but it never refers to dates.
Ex. select median(birth_dt) from some_table;
by dpecor
by smithjj
A median is the middle of a series. The date value doesn't enter into it. You'll want to do a Query (DISTINCT) to get the unique values of your date. Then do a COUNT to get the number of distinct date. Then divide by 2 and do an INT (greatest integer within the result) to get the median.
Jeff Smith
by dpecor
by dim
1. Try this
select max(STARTDATE) from (
select STARTDATE
from (select STARTDATE from TAB order by startdate) where rownum <= (select count(*) from TAB));
2. In Oracle v.8.1.6+ you may use analytical function:
select max(STARTDATE) from
(select STARTDATE,
percent_rank() over(order by STARTDATE) as proc
from tab)
where proc <= 0.50
Good luck
by dpecor
by dpecor
This discussion is closed to new replies.
Sort by Trending