Re: Puzzler: Finding median value with SQL?
Date: 25 Mar 1994 15:40:46 -0600
Message-ID: <2mvlou$jh4_at_tamsun.tamu.edu>
In article <2mt2qaINNshc_at_dns1.nmsu.edu>, Mcrider <mcrider_at_acca.nmsu.edu> wrote:
>
>How can one find a median value in Oracle? In a statistical distribution
>the median value is the value of the variate above and below which equal
>numbers of items lie.
>
>As an example, assume we have a table containing a column of numbers that
>all fall within a certain range. If we could order all the values in the
>column and identify the value at the middle of the column, we would know
>the median value. We might suppose that we could simply make use of the
>'ROWNUM' pseudo-column with the 'ORDER BY' clause in a 'SELECT' statement.
>However, the value of rownum is assigned *before* the 'ORDER BY' clause
>takes effect, so this method won't work.
>
>Does anyone have a suggestion?
>
>--
>John Crain mcrider_at_acca.nmsu.edu
The following PL/SQL script will get the median for u. I don't think it
is possible to do it in SQL alone.
Script follows:
declare cursor foo_cursor is select col1 from foo order by col1; temp number(3);
temp1 foo.col1%type;
begin
select count(col1) into temp from foo order by col1; temp:=temp/2;
open foo_cursor;
for i in 1..temp
loop
fetch foo_cursor into temp1;
end loop;
close foo_cursor;
/* temp1 contains the median */
end;
/
Thanks and Regards
Mahesh Vallampati
M.S. In EE
Dept.of Electrical Engineering,
Texas A & M University.
Ph:(409)862-1070
/* Thy Shall Inherit and Distribute */ Received on Fri Mar 25 1994 - 22:40:46 CET