This is incorrect. With the SQL analytic functions this is possible through
PERCENTILE_DISC(0.5).
HTH.
Finn
"Jusung Yang" <JusungYang_at_yahoo.com> wrote in message
news:130ba93a.0210090916.399365ef_at_posting.google.com...
> ORACLE does not yet have a "median" function. So you have to write
> your own. Many ways you can code it. This is one example:
>
>
> SQL> select * from t2;
>
> C1
> ----------
> 2
> 4
> 5
> 8
> 11
> 0
> 5
> 0
> 0
> 0
> 0
>
> 11 rows selected.
>
> SQL> select c1, row_number() over (order by c1) rn, ceil(count(1)
> over()/2) mdn from t2;
>
> C1 RN MDN
> ---------- ---------- ----------
> 0 1 6
> 0 2 6
> 0 3 6
> 0 4 6
> 0 5 6
> 2 6 6
> 4 7 6
> 5 8 6
> 5 9 6
> 8 10 6
> 11 11 6
>
> 11 rows selected.
>
> SQL> select c1 from (select c1, row_number() over (order by c1) rn,
> ceil(count(1) over()/2) mdn from
> t2) where rn=mdn;
>
> C1
> ----------
> 2
>
> SQL> insert into t2 values(9);
>
> 1 row created.
>
> SQL> select c1, row_number() over (order by c1) rn, ceil(count(1)
> over()/2) mdn from t2;
>
> C1 RN MDN
> ---------- ---------- ----------
> 0 1 6
> 0 2 6
> 0 3 6
> 0 4 6
> 0 5 6
> 2 6 6
> 4 7 6
> 5 8 6
> 5 9 6
> 8 10 6
> 9 11 6
>
> C1 RN MDN
> ---------- ---------- ----------
> 11 12 6
>
> 12 rows selected.
>
> SQL> select c1 from (select c1, row_number() over (order by c1) rn,
> ceil(count(1) over()/2) mdn from
> t2) where rn=mdn;
>
> C1
> ----------
> 2
>
> SQL>
>
>
> - Jusung Yang
>
>
> Laly KATTOOR <laly.kattoor_at_bnpparibas.com> wrote in message
news:<2002109-101537-355947_at_foorum.com>...
> > Hi,
> >
> > I would like to know whether there is a trick to compute median in a sql
query ?
> >
> >
> > Thanks by advance for your answers.
> >
> >
> > Laly.
Received on Thu Oct 10 2002 - 03:56:29 CDT
