Re: Median function in SQL

In article <34FD6DD6.3081E539_at_iname.com>, S Chavali <nospam_at_iname.com> wrote:
>Dave,
>I believe the snippet of code below will return the *mode* value, not the
>*median*. If I remember my statistics right, *median* is the "middle" value is a
>list of ascending/descending values while the *mode* value is the number that
>occurs most often in such a list.
>Anybody else have any other thoughts ?
>

Something like
select revenue from company_financials
where rownum = round((select count(*) from company_financials)/2) order by revenue; ?

Haven't tried it, just thinking.

>Srini
>(to reply, change "nospam" to "chavali" in my email address. :-) )
>
>Dave McRae wrote:
>
>> GDay Kevin,
>> Being at home today, I don't have access to ORACLE to check it out, but I
>> reckon
>>
>> select revenue, max(cntr) from
>> (select revenue, count(*) as cntr
>> from company_financials
>> where ....
>> group by revenue)
>>
>> hope this works :)
>> --
>> David McRae dmcrae_at_dynamite.com.au
>> Canberra 02 6239 4247
>> Kevin Merritt wrote in message
>> <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
>

-- 
These opinions are my own and not necessarily those of Information Quest
jgarry@eiq.com                           http://www.informationquest.com
http://ourworld.compuserve.com/homepages/joel_garry
"See your DBA?"  I AM the @#%*& DBA!