Re: computing median

Your counter-example in another post to my suggestion for using ntile(2) to get the median is a useful reminder of paying attention to detail.

An NTILE() operation still has a fair chance of being more efficient than counting the number of rows, then fetching them in order until you get the appropriate one.

Your example reminded me that median is:

    For even number of items, the mean of the     middle pair; for odd number of items the     middle item.

I think I posted an example of expressing this using NTILE() some time around Dec 2000. You may be able to find it on http://groups.google.com

--
Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Practical Oracle 8i:  Building Efficient Databases
Publishers:  Addison-Wesley

Reviews at: http://www.jlcomp.demon.co.uk/book_rev.html



Frank Hubeny wrote in message <3AE7BF12.6CCE17B5_at_ntsource.com>...


>I would agree that using Excel is not the best way to get the median,

 especially


>if there are millions of records involved.  However, the suggestion to use

 the


>cume_dist function does not always give the expected answer.  (Perhaps I

 just


>don't understand how it is being used.)

>

>Suppose I have a table, called testmedian with one column, A, and it

 contains


>two rows with 1 and 10 as values for A.  The median of 1 and 10 is their

 average


>or 5.5.  (This is what Excel says it is at any rate.)

>

>However, when I do the cume_dist over A, I get the following:

>

>SQL> select a, cume_dist() over (order by a) result from testmedian;

>

>         A     RESULT

>---------- ----------

>         1         .5

>        10          1

>

>This would imply that 1 is the median, which is incorrect.

>

>It is also possible that cume_dist does not return any value at the .5

 level.


>This would occur, for example, if the numbers in the table were 1,1,10.

>

>SQL> select a, cume_dist() over (order by a) result from testmedian;

>

>         A     RESULT

>---------- ----------

>         1 .666666667

>         1 .666666667

>        10          1

>

>