Re: Gropus

From: <mrdjmagnet_at_aol.com>
Date: Fri, 9 Jan 2009 08:47:58 -0800 (PST)
Message-ID: <d3a58837-a3bc-4b37-9e03-4f013081454e_at_i20g2000prf.googlegroups.com>

On Jan 9, 10:20 am, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
> <mrdjmag..._at_aol.com> a écrit dans le message de news: aa21a42e-4ded-42e2-aa71-568394ed6..._at_f40g2000pri.googlegroups.com...
> |
> | I need to do something really weird with some numbers.  I need to
> | seperate them into quintiles.  Say for example I have these numbers:
> |
> | 5,9,3,6,5,1,34,8,54,98,90,34,55
> |
> | with all these numbers I need to take the top 20% and put them into
> | group 1, the next 20% into group 2, etc......
> |
> | Was looking to find some analytical function....but no luck.  So,
> | PLSQL must be the choice.  But, I'm having trouble with the
> | logic......
> |
> | Anyone smarter??
> |
> |
>
> Have a look at NTILE function.
>
> Regards
> Michel

Ok, I do not think that is what I want:

CREATE TABLE x (c1 VARCHAR2(2), c2 NUMBER);

insert into x values ('A'2);
insert into x values ('A'12);
insert into x values ('A'2);
insert into x values ('A',22);
insert into x values ('A',62);
insert into x values ('A',92);
insert into x values ('A',34);
insert into x values ('A',76);
insert into x values ('A',9);
insert into x values ('A',3);
insert into x values ('A',25);
insert into x values ('A',55);
insert into x values ('A',67);
insert into x values ('A',87);
insert into x values ('A',99);
insert into x values ('A',94);

SELECT c2, c1, NTILE(4) OVER (ORDER BY c1) FROM x;

        C2 C1 NTILE(4)OVER(ORDERBYC1)

---------- -- -----------------------
        22 A                        1
        62 A                        1
        92 A                        1
        34 A                        1
        76 A                        2
         9 A                        2
        94 A                        2
        25 A                        3
        55 A                        3
        67 A                        3
        87 A                        4
        99 A                        4
         3 A                        4

What I want is to take the top 20% number and put them into bucket 1, the next 20% into bucket 2, the next into bucket 3, bucket 4 and the last (lowest) 20% into 5.

So, I am ranking them based on the entire group of values. Received on Fri Jan 09 2009 - 10:47:58 CST