Re: Calculate a median

Oracle obviously doesn't have a median function so... the trick is to force Oracle to use an index to read the source data...which then allows the rownum pseudocolumn to act as a sorted record counter. Taking
the max(rownum)+1/2 gives the median's record number. This can then be used within a subquery. The outer query selects the maximum value (forced again to
read through an index so the data is sorted when retrieved) where the rownum is
not greater than that returned by the subquery. This isn't particularly efficient if you have large tables! A simplified example may help:
To find the median value in the SAL column of the EMP table:

1. create an index on the SAL column
2. SELECT MAX(SAL) MEDIAN_SAL FROM EMP WHERE SAL = 0 [this forces an index read] AND ROWNUM = (SELECT ((MAX(ROWNUM)+1)/2) FROM EMP WHERE SAL = 0)

Francisco Caliz <fcc_at_jerez.micro.lucent.com> wrote in message news:37734236.520F51D6_at_jerez.micro.lucent.com...
>
> A very simple question:
>
> Anybody knows how to calculate a median in Oracle ?
>
> Thank you very much
> Francisco Caliz Carretero
> Lucent Technologies Spain
Received on Fri Jun 25 1999 - 18:51:08 CEST