Re: Gropus

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

You need to order by the column to be ranked: SELECT c2, c1,

   NTILE(5) OVER (PARTITION BY c1 ORDER BY c2) FROM x;

I don't know if you really need the PARTITION BY, this starts a new ranking for each different value, similar to GROUP BY.

But if you want statistical quantiles, the NTILE function will not return the expected result set, because two rows with the same value c2 might be in different quantiles.

Better use this calculation instead:
(RANK() OVER (PARTITION BY c1 ORDER BY c2 DESC) - 1) * 5   / COUNT(*) OVER (PARTITION BY c1)

Dieter Received on Fri Jan 09 2009 - 14:23:40 CST