Re: Median function in SQL

From: tzadkiel <tzadkiel_at_surfnetcity.com.au>
Date: 1998/03/09
Message-ID: <01bd4b39$2fdeedc0$66ce6ccb@default>#1/1

this was fun. one solution is like this:

select ranked.company, ranked.revenue

from 	(select a.company, a.revenue, count(*) ranking
	from companys a, companys b
	where a.revenue>b.revenue
	  or (a.revenue=b.revenue and a.rowid>=a.rowid)) ranked,
	(select round(count(*)/2) median_point
	from companys) mp

where ranked.ranking=mp.median_point

the first in-line view is a general way to do rankings - what we all intuitively think "rownum" should do, but doesn't. once the table is ranked, it's simple for the second view to locate what the median record number is.

this is obviously table/field specific, but we should be able to use the technique with the dynamic SQL packages to give us a general function something like :

"MEDIAN(table_name, field_name)"

share and enjoy.

Kevin Merritt <kevin_merritt_at_genevaco.com> wrote in article <01bd4482$5787d980$c1015c91_at_KMERRITT.GENEVACO.COM>...
> Does anyone have any idea how to implement a MEDIAN function in Oracle?
 For
> example, I would like to perform the following SQL SELECT statement:
>
> SELECT MEDIAN(revenue) FROM company_financials WHERE revenue > 1000000
>
> I can write a stored procedure for each table/column I want to perform
 this
> for, but I would like to somehow extend the built-in environment to have
> the ultimate flexiblity.
>
> Thanks in advance
>
Received on Mon Mar 09 1998 - 00:00:00 CST