Discussion Locked

Finding median date in Oracle

0 helpful
7 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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;

7 replies

by prashantattray

the median function you are referring is something related to the median of a timeseries. Therefore you can refer to the function TSMedian. That will give you clear idea. Or refer to the Oracle8i TimeSeries Users Guide for the complete syntax andexample.

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 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

This discussion is closed to new replies.