Re: Gropus

<mrdjmagnet_at_aol.com> a écrit dans le message de news: 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.

Are you sure you want to order by C1?
Post the result you want.

Regards
Michel Received on Fri Jan 09 2009 - 12:53:35 CST