Re: Ntile Function

This is very close BUT the percentiles are not ordered by amount. When I try to order the result by amount,
I get an error saying amount is not part of the group by function - (which it can't be). Percentiles are arrayed from lowest to hightest.

Here's the result from the code:

ID % Amount Should Be:

27199203   10 	$101				 10	$ 80
27199203   20 	$109				 20	$ 81
27199203   30 	$117				 30	$ 81
27199203   40 	$117				 40	$ 98
27199203   50 	$121.58				 50	$ 98
27199203   60 	$ 80				 60	$101
27199203   70  	$ 81				 70	$109
27199203   80 	$ 81				 80	$117
27199203   90 	$ 98				 90	$117	
27199203  100	$ 98				100	$121.58

How it is possible to order by Ascending Amount?

Jonathan Lewis wrote:
>
> NTILE does allow you to generate percentiles,
> but not in the way you want from the data you
> have. You need to have seven occurrences of
> 27199203 $80.00
> rather than one occurrence of
> 27199203 $80.00 7
>
> Then you could query:
>
> select id, amount, ntile(100) over (partition by id order by amount) as
> percentile
> from normalised_table
> order by id, percentile
>
> or perhaps in your case, where you have a lower granularity:
>
> select id, amount, 10 * ntile(10) over (partition by id order by amount)
> as percentile
> from normalised_table
> order by id, percentile
>
> Then - if you want to print only the boundaries:
>
> select id, percentile, max(amount)
> from (
> select id, amount, 10 * ntile(10) over (partition by id order by
> amount) as percentile
> from normalised_table
> order by id, percentile
> )
> group by id, percentile
> order by id, percentile
> ;
>
> BTW - Correct me if I'm wrong, but don't percentiles
> go from 0 to 99, or 1 to 100, rather than 0 to 100 ?
>
> --
> Jonathan Lewis
> Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
>
> Practical Oracle 8i: Building Efficient Databases
>
> Publishers: Addison-Wesley
> 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
Received on Fri Dec 22 2000 - 08:57:09 CST