Re: Puzzler: Finding median value with SQL?

Paul Roberts (proberts_at_informix.com) wrote:
: >>How can one find a median value in Oracle? In a statistical distribution
: >>the median value is the value of the variate above and below which equal
: >>numbers of items lie.
 

: >The following PL/SQL script will get the median for u. I don't think it
: >is possible to do it in SQL alone.
 

: Here's how you could do it with Informix-SQL. If anyone thinks that the use
: of temp tables is "cheating" then I invite them to either dismiss this as a
: non-solution, or see if they can't find a way to modify it to eliminate the
: temp table use. If it weren't so late on a Friday, I would be exploring the
: latter avenue myself....
:
: Paul

Well, I'll give it a try...

Lets say we have a table FOO:

        create table FOO (BAR NUMBER);

Now we want to find the median of BAR in table FOO. I think the following query should do the trick (in Oracle at least):

        select a.BAR
        from FOO a, FOO b
        where (a.BAR < b.BAR(+)) or
              (a.BAR = b.BAR and
               a.ROWID > b.ROWID )
        having count(distinct b.ROWID)=(select trunc(count(*)/2) from FOO)
        group by a.BAR, a.ROWID;

What this should do is return the value of BAR in FOO that has count(*)/2 records greater than or equal to BAR.

If your table has a primary key then you can replace all the ROWID stuff with the primary key. All we need is something that will uniquely identify each row so that there is no double counting when a number occurs more than once.

The outer join is not really needed but what is does is makes sure that every row in the table will appear with a count of the records greater than it. So you can probably use this query to find the Nth largest number in the list by looking for the record with N-1 records greater than it.

hope this helps...
Tom

--
  |\                    +---------------------------------+
  | ) ----------------- | Tommy Hui                       | The views expressed
 / /  Energy            | Programmer/Analyst              | here are solely  my
( (    Management       |_________________________________| own  and  in no way
 \ \    Solutions Corp. |                                 | reflect  the  views
  | ) ----------------- | ems2_at_delphi.calgary.chevron.com | of my employer.
  |/                    +---------------------------------+