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>...


>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

>