Re: Select Construct

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Tue, 16 Oct 2007 22:43:48 +0200
Message-ID: <47152284.8080003@gmail.com>

DJH schrieb:
> Is there a way in Oracle SQL to extract the median of the 3 teams and
> their scores but only using the latest scores and ignoring older scores?
>
> select median(Score) from scores where ... etc
>
> How can this be done? Again the three distinct teams but the median of
> the latest 3 scores. The table will only have these three distinct teams
> but scores from different dates.
>
> Also this is an example. I am not a degenerate gambler or online gamer
> etc. Thanks!
>
> Scores Table and data:
>
> Team Score Date
>
> Giants 15 10/11/2007
> Cowboys 20 10/1/2007
> Eagles 30 10/9/2007
> Giants 5 9/11/2007
> Cowboys 32 9/1/2007
> Eagles 13 9/9/2007

Assuming you are on 10g:

SQL> with t as (

2 select 'Giants ' team, 15 score, date '2007-10-11' dt from dual union all

   3  select 'Cowboys ' , 20 , date  '2007-10-11' from dual union all
   4  select 'Eagles ' , 30 , date  '2007-10-11' from dual union all
   5  select 'Giants ' , 5 , date  '2007-10-11' from dual union all
   6  select 'Cowboys ' , 32 , date  '2007-10-11' from dual union all
   7  select 'Eagles ' , 13 , date  '2007-10-11' from dual

   8 )
   9 select median(score)
  10 from (select team,score,max(dt) dt from t group by team,score   11 )
  12 /

MEDIAN(SCORE)

Respectively, for 9i (with the same testdata):

select percentile_cont(0.5) within group (order by score)

from (select team,score,max(dt) dt from t group by team,score )
/

Best regards

Maxim Received on Tue Oct 16 2007 - 15:43:48 CDT