Re: median value

: SELECT MAX (value)
: FROM table
: WHERE value <= (
: SELECT AVG (value)
: FROM table
: )
 

: This might not be the fastest way but it works.

The below is a worked example in which the order of insertion is correct:

create table foo (value number(1));

insert into foo values(9);
insert into foo values(7);
insert into foo values(1);

[Quoted] select max(value) from foo where value <= (select avg(value) from foo)

MAX(VALUE)

         1

Hmm, doesn't look quite right to me.  

You need to know how many elements in your data set and then you need to have your data set ordered in some way to make the test meaningful. Note by 'ordered' I do not necessarily mean numeric ordering of the value in question, the data set could be ordered by some other key.

Consider writing a PL/SQL (or other programming language) function to do this. You could either read the data into PL/SQL tables and keep a count of how many rows returned or you could count the number of elements in your data set and then acess the median value in a loop exiting when you hit that row. Without knowing your application and the context(s) that you wish to use this functionality in, it would be difficult to advise as to what is the more efficient. The coding of this function is trivial.

Remember to cater for odd & even numbers of data.

IAP Received on Thu Dec 12 1996 - 00:00:00 CET