| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Median function in SQL
Hi.
I've tried it and it works with this group by:
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 >= b.rowid)
group by a.rowid, a.revenue, a.company) ranked,
(select round(count(*)/2) median_point
from companys) mp
Great thing !!!
The first inline-view seems to me to be a solution
for many ordered rownum queries!
Greez,
Angelo.
tzadkiel wrote:
>
> 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.
>
-- ----------------------------------------------------------------- Angelo Cavallaro / / / \ TECTUM Beratungsgesellschaft ORACLE-SW-Berater / / / \ für Informationsverarbeitung ancavall.NOSPAM / / /-------\ Tel: 0711/99073-10 @aol.com /__/__/_________\ Fax: 0711/99073-99 ----------------------------------------------------------------- Visit the Home of the Green Ribbon Campaign: http://www.ponce.oisoft.com/ -----------------------------------------------------------------Received on Mon Mar 09 1998 - 00:00:00 CST
 |
 |