From: "Jonathan Lewis" <jonathan@jlcomp.demon.co.uk>
Newsgroups: comp.databases.oracle.misc,comp.databases.oracle.server
Subject: Re: Finding the median
Date: Tue, 12 Dec 2000 21:36:04 -0000
Message-ID: <976656783.29371.1.nnrp-09.9e984b29@news.demon.co.uk>
References: <3A27F23C.3A111F35@weinerfamily.org> <975707338.5062.0.nnrp-01.9e984b29@news.demon.co.uk> <EfZV5.36957$3u1.9031597@news3.rdc1.on.home.com> <975744165.27815.0.nnrp-04.9e984b29@news.demon.co.uk> <975748133.25155.0.nnrp-07.9e984b29@news.demon.co.uk> <3A2BB34E.909120E5@weinerfamily.org>
NNTP-Posting-Host: jlcomp.demon.co.uk
X-NNTP-Posting-Host: jlcomp.demon.co.uk:158.152.75.41
X-Trace: news.demon.co.uk 976656783 nnrp-09:29371 NO-IDENT jlcomp.demon.co.uk:158.152.75.41
X-Complaints-To: abuse@demon.net
X-Newsreader: Microsoft Outlook Express 4.72.3110.5
X-MimeOLE: Produced By Microsoft MimeOLE V4.72.3110.3
Lines: 71



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@weinerfamily.org wrote in message <3A2BB34E.909120E5@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
>