Re: how to compute median ?

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 Wed Oct 09 2002 - 12:16:36 CDT