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:
- create an index on the SAL column
- 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)
Regards
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 - 11:51:08 CDT
