Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Finding the median
I'm not sure that I would want to use it, but the following matches your algorithm.
select
decode(sign(ct_high - ct_low), 0, (max_low + min_high) / 2, 1, min_high, -1, max_low ) median from ( select sum(decode(bi_tile,1,max_low)) max_low, sum(decode(bi_tile,2,min_high)) min_high, sum(decode(bi_tile,1,ct)) ct_low, sum(decode(bi_tile,2,ct)) ct_high from ( select bi_tile, max(val1) max_low, min(val1) min_high, count(*) ct from ( select val1, ntile(2) over(order by val1) bi_tile from table1 ) group by bi_tile ) )
create table table1 (val1 number);
with rows for: 1,2,2,4,4 this returns 2, with rows for: 1,2,2,4,4,7 it returns 3, with rows for: 1,2,2,4,4,7,8 it returns 4
-- Jonathan Lewis Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk Practical Oracle 8i: Building Efficient Databases Publishers: Addison-Wesley Book bound date: 8th Dec 2000 See a first review at: http://www.ixora.com.au/resources/index.htm#practical_8i More reviews at: http://www.jlcomp.demon.co.uk/book_rev.html sw_at_weinerfamily.org wrote in message <3A2BB34E.909120E5_at_weinerfamily.org>...Received on Tue Dec 12 2000 - 15:36:04 CST
>Could you give me an example? I used the following psuedo algorithm to find
>median:
>num = number of elements
> IF mod(num,2) <> 0 THEN
> median = ROW_NUMBER(num/2 + 1)
>ELSE
> median = ROW_NUMBER((num/2))+ROW_NUMBER((num/2) + 1))/2
>END IF
>