General discussion

Locked

Finding median date in Oracle

By dpecor ·
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;

This conversation is currently closed to new comments.

7 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Finding median date in Oracle

by prashantattray In reply to Finding median date in Or ...

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.

Collapse -

Finding median date in Oracle

by dpecor In reply to Finding median date in Or ...

Poster rated this answer

Collapse -

Finding median date in Oracle

by smithjj In reply to Finding median date in Or ...

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

Collapse -

Finding median date in Oracle

by dpecor In reply to Finding median date in Or ...

Poster rated this answer

Collapse -

Finding median date in Oracle

by dim In reply to Finding median date in Or ...

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

Collapse -

Finding median date in Oracle

by dpecor In reply to Finding median date in Or ...

Poster rated this answer

Collapse -

Finding median date in Oracle

by dpecor In reply to Finding median date in Or ...

This question was closed by the author

Back to Networks Forum
7 total posts (Page 1 of 1)  

Related Discussions

Related Forums