Re: Ntile Function

For the moment I'm baffled, I can't spot the error.

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

>>     ;

The line marked **** is redundant by the way.

The bit which goes
>> ntile(10) over (
>> partition by id
>> order by amount
>> ) as percentile

means that Oracle should take all rows for an ID, order them by amount, then add 'decile' values to each row - so the order of the 'deciles' and the order of the amount should match, which means that you can't possibly get ---

>27199203 50 $121.58
>27199203 60 $ 80

Aha !! Flash of inspiration - is your amount stored as a CHARACTER rather than a
number ??? Your output appears to have a character-based ordered

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



JShrimip, Jr. wrote in message <3A436BC5.2714EB09_at_hotmail.com>...


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