Re: Select Construct

On Oct 16, 2:46 pm, DJH <NOS..._at_NOSPAM.COM> wrote:
> 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

Like this:

SQL> create table scores(

  2          team varchar2(20),
  3          score number,
  4          game_day date);

Table created.

SQL>
SQL> insert all
  2 into scores
  3 values('Giants',15,to_date('10/11/2007','MM/DD/YYYY'))   4 into scores
  5 values('Cowboys',20,to_date('10/1/2007','MM/DD/YYYY'))   6 into scores
  7 values('Eagles',30,to_date('10/9/2007','MM/DD/YYYY'))   8 into scores
  9 values('Giants',5,to_date('09/11/2007','MM/DD/YYYY'))  10 into scores
 11 values('Cowboys',32,to_date('09/1/2007','MM/DD/YYYY'))  12 into scores
 13 values('Eagles',13,to_date('09/9/2007','MM/DD/YYYY'))  14 into scores
 15 values('Giants',12,to_date('08/11/2007','MM/DD/YYYY'))  16 into scores
 17 values('Cowboys',27,to_date('08/1/2007','MM/DD/YYYY'))  18 into scores
 19 values('Eagles',34,to_date('08/9/2007','MM/DD/YYYY'))  20 into scores
 21 values('Giants',56,to_date('07/11/2007','MM/DD/YYYY'))  22 into scores
 23 values('Cowboys',12,to_date('07/1/2007','MM/DD/YYYY'))  24 into scores
 25 values('Eagles',43,to_date('07/9/2007','MM/DD/YYYY'))  26 select * from dual;

12 rows created.

SQL>
SQL> select median(score) from scores where team = 'Giants' and game_day > to_date('07/31/2007', 'MM/DD/YYYY')   2 /

MEDIAN(SCORE)

           12

SQL>
SQL> select team, median(score) from scores where game_day > to_date('07/31/2007', 'MM/DD/YYYY')
  2 group by team
  3 /

TEAM                 MEDIAN(SCORE)
-------------------- -------------
Cowboys                         27
Eagles                          30
Giants                          12

SQL>
SQL> select team, median(score) from scores where game_day > to_date('07/01/2007', 'MM/DD/YYYY')
  2 group by team
  3 /

TEAM                 MEDIAN(SCORE)
-------------------- -------------
Cowboys                         27
Eagles                          32
Giants                        13.5

SQL>
SQL> select team, median(score) from scores where game_day > to_date('08/01/2007', 'MM/DD/YYYY')
  2 group by team
  3 /

TEAM                 MEDIAN(SCORE)
-------------------- -------------
Cowboys                         26
Eagles                          30
Giants                          12

SQL>
SQL> select team, median(score) from scores where game_day > to_date('09/01/2007', 'MM/DD/YYYY')
  2 group by team
  3 /

TEAM                 MEDIAN(SCORE)
-------------------- -------------
Cowboys                         20
Eagles                        21.5
Giants                          10

SQL> David Fitzjarrell Received on Tue Oct 16 2007 - 15:35:10 CDT