Re: median problems

From: Steve Howard <stevedhoward_at_gmail.com>
Date: Sat, 27 Oct 2007 15:22:19 -0000
Message-ID: <1193498539.347007.232870@22g2000hsm.googlegroups.com>

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> Received on Sat Oct 27 2007 - 10:22:19 CDT