Re: Puzzler: Finding median value with SQL?
>>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.
>The following PL/SQL script will get the median for u. I don't think it
>is possible to do it in SQL alone.
Here's how you could do it with Informix-SQL. If anyone thinks that the use
of temp tables is "cheating" then I invite them to either dismiss this as a
non-solution, or see if they can't find a way to modify it to eliminate the
temp table use. If it weren't so late on a Friday, I would be exploring the
latter avenue myself....
create temp table big_list ( a_number smallint ) ;
insert into big_list values ( 10 ) ;
insert into big_list values ( 10 ) ;
insert into big_list values ( 10 ) ;
insert into big_list values ( 10 ) ;
insert into big_list values ( 20 ) ;
insert into big_list values ( 22 ) ;
insert into big_list values ( 23 ) ;
insert into big_list values ( 24 ) ;
insert into big_list values ( 25 ) ;
insert into big_list values ( 26 ) ;
insert into big_list values ( 27 ) ;
insert into big_list values ( 28 ) ;
insert into big_list values ( 29 ) ;
select unique * from big_list into temp t1 ;
select t1.a_number, count(*) cnt
from t1, big_list
where t1.a_number >= big_list.a_number
group by 1
having count(*) >= ( select count(*) from big_list ) / 2
into temp t2 ;
select min(a_number) from t2 ;
This last query returns "23", which is correct.
Paul