Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: median problems

Re: median problems

From: <fitzjarrell_at_cox.net>
Date: Mon, 29 Oct 2007 05:53:47 -0700
Message-ID: <1193662427.562389.283120@o3g2000hsb.googlegroups.com>


On Oct 27, 10:33 am, Steve Howard <stevedhow..._at_gmail.com> wrote:
> On Oct 27, 11:22 am, Steve Howard <stevedhow..._at_gmail.com> wrote:
>
>
>
>
>
> > On Oct 26, 12:35 pm, DJH <NOS..._at_NOSPAM.COM> wrote:
>
> > > Is there a way in Oracle SQL to extract the median of the 3 tests (MATH
> > > READING AND WRITING) and
> > > their scores but only using the latest scores and ignoring older scores?
>
> > > I want to ignore their oldest math score at the bottom etc..
>
> > > select median(Score) from scores where ... etc and emp=JONES etc
>
> > > How can this be done? Again the three distinct grade/test CATEGORIES but
> > > the median of the latest 3 scores. The table will only have these three
> > > distinct teams but scores from different dates.
>
> > > Scores Table and data:
>
> > > Team TestGrade TstDate EMP
>
> > > MATH 15 10/11/2007 JONES
> > > READING 20 10/1/2007 JONES
> > > WRITING 30 10/9/2007 JONES
> > > MATH 38 9/9/2007 JONES <-ignore OLD
> > > WRITING 40 8/9/2006 JONES <-IGNORE OLD
>
> > > MATH 5 9/11/2007 SMITH
> > > READING 32 9/1/2007 SMITH
> > > WRITING 13 9/9/2007 SMITH
> > > READING 45 8/9/2007 SMITH <-ignore OLD
>
> > > Remember the last math score or reading score should not count. I just
> > > want the median of the latest (math, reading and writing) combined using
> > > the latest results and this should be found FOR A particular employee
> > > given to the query.
>
> > > so the plsql function i will be writing will be
>
> > > function getmedian(pvEMP) ...
>
> > > and returning select median()... where emp=pvEMP etc.
>
> > > Thanks!!
>
> > As Mathias asked, what version? Try the following if on 10.x...
>
> > SQL> desc tests
> > Name Null? Type
> > ----------------------------------------- --------
> > ----------------------------
> > STUDENT VARCHAR2(10)
> > SCORE NUMBER
> > TEST_DATE DATE
>
> > SQL> select * from tests;
>
> > STUDENT SCORE TEST_DATE
> > ---------- ---------- ---------
> > tom 80 26-OCT-07
> > tom 90 25-OCT-07
> > tom 20 24-OCT-07
> > tom 80 23-OCT-07
> > lisa 60 17-OCT-07
> > lisa 50 07-OCT-07
> > lisa 55 27-SEP-07
> > lisa 55 17-SEP-07
>
> > 8 rows selected.
>
> > SQL> select student,
> > 2 avg(score)
> > 3 from (select student,score,
> > 4 row_number() over
> > 5 (partition by student order by student,test_date
> > desc) rn
> > 6 from tests)
> > 7 where rn <= 3
> > 8 start with rn = 1
> > 9 connect by prior rn = rn - 1 and prior student = student
> > 10 group by student
> > 11 order by student
> > 12 /
>
> > STUDENT AVG(SCORE)
> > ---------- ----------
> > lisa 55
> > tom 63.3333333
>
> > SQL> select student,
> > 2 median(score)
> > 3 from (select student,score,
> > 4 row_number() over
> > 5 (partition by student order by student,test_date
> > desc) rn
> > 6 from tests)
> > 7 where rn <= 3
> > 8 start with rn = 1
> > 9 connect by prior rn = rn - 1 and prior student = student
> > 10 group by student
> > 11 order by student
> > 12 /
>
> > STUDENT MEDIAN(SCORE)
> > ---------- -------------
> > lisa 55
> > tom 80
>
> > SQL>
>
> ...BTW, you'll have to add the class, but that should get you started- Hide quoted text -
>
> - Show quoted text -

Interesting that you didn't choose to use the data the OP provided, as the solution is much simpler than you illustrate:

SQL> create table scores(

  2          team varchar2(10),
  3          testgrade number,
  4          tstdate date,
  5          emp varchar2(20))

  6 /

Table created.

SQL>
SQL> insert all
  2 into scores
  3 values('MATH',15,to_date('10/11/2007','MM/DD/YYYY'),'JONES')   4 into scores
  5 values('WRITING',30,to_date('10/09/2007','MM/DD/YYYY'),'JONES')   6 into scores
  7 values('READING',20,to_date('10/01/2007','MM/DD/YYYY'),'JONES')   8 into scores
  9 values('MATH',5,to_date('09/11/2007','MM/DD/YYYY'),'SMITH')  10 into scores
 11 values('MATH',38,to_date('09/09/2007','MM/DD/YYYY'),'JONES')  12 into scores
 13 values('WRITING',13,to_date('09/09/2007','MM/DD/YYYY'),'SMITH')  14 into scores
 15 values('READING',32,to_date('09/01/2007','MM/DD/YYYY'),'SMITH')  16 into scores
 17 values('READING',45,to_date('08/09/2007','MM/DD/YYYY'),'SMITH')  18 into scores
 19 values('WRITING',40,to_date('08/09/2006','MM/DD/YYYY'),'JONES')  20 select * from dual;

9 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select emp, median(testgrade)
  2 from scores
  3 where emp = 'JONES'
  4 and tstdate > to_date('09/09/2007','MM/DD/YYYY')   5 group by emp
  6 /

EMP                  MEDIAN(TESTGRADE)
-------------------- -----------------
JONES                               20

SQL>
SQL> select emp, median(testgrade)
  2 from scores
  3 where emp = 'SMITH'
  4 and tstdate > to_date('08/09/2007','MM/DD/YYYY')   5 group by emp
  6 /

EMP                  MEDIAN(TESTGRADE)
-------------------- -----------------
SMITH                               13

SQL>
SQL> select emp, team, tstdate, testgrade   2 from scores
  3 where (emp, team, tstdate) in
  4 (select emp, team, max(tstdate) from scores group by emp, team)   5 order by emp;

EMP                  TEAM       TSTDATE    TESTGRADE
-------------------- ---------- --------- ----------
JONES                READING    01-OCT-07         20
JONES                WRITING    09-OCT-07         30
JONES                MATH       11-OCT-07         15
SMITH                READING    01-SEP-07         32
SMITH                MATH       11-SEP-07          5
SMITH                WRITING    09-SEP-07         13

6 rows selected.

SQL>
SQL> select emp, median(testgrade)
  2 from scores
  3 where (emp, team, tstdate) in
  4 (select emp, team, max(tstdate) from scores group by emp, team)
  5 group by emp
  6 /

EMP                  MEDIAN(TESTGRADE)
-------------------- -----------------
JONES                               20
SMITH                               13

Of course this is doing the homework for some student who claims he isn't, but I'm tired of his complaining and his laziness.

David Fitzjarrell Received on Mon Oct 29 2007 - 07:53:47 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US