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

<mrdjmagnet (AT) aol (DOT) com> a écrit dans le message de news: aa21a42e-4ded-42e2-aa71-568394ed6a22...oglegroups.com...
Have a look at NTILE function.

Regards
Michel

On Jan 9, 10:20*am, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
This looks similar. I'll have to play with it because I've never
heard of this function before.

It does not say anything about ranking and putting into buckets. It
also says some things about having to divide evenly into buckets,
which will probably not be the case in my situation.......

But I'll see what it does.

On Jan 9, 10:20*am, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
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.

<mrdjmagnet (AT) aol (DOT) com> a écrit dans le message de news: 25d75347-6e27-4ef2-af9b-c42dcafae90a...oglegroups.com...
On Jan 9, 10:20 am, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
This looks similar. I'll have to play with it because I've never
heard of this function before.

It does not say anything about ranking and putting into buckets. It
also says some things about having to divide evenly into buckets,
which will probably not be the case in my situation.......

But I'll see what it does.

-----------------------------------

It depends on what you meant with "top 20%"...
20% in number of rows or value (with greatest-least=100%) or...?

Regards
Michel

<mrdjmagnet (AT) aol (DOT) com> a écrit dans le message de news: d3a58837-a3bc-4b37-9e03-4f013081454e...oglegroups.com...
On Jan 9, 10:20 am, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
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