Question about getting the Top N values or Bottom N values

[Rustican]

Is there a function supported by Oracle that will ret only the Top N or Bottom N of a value?

For example If i had a class of students and i wanted 3 columns of grades. The first column are all the grades, the second column are all the grades at the top 3 and the third are the bottom 3 grades at the bottom of the class.

so my query return should look like this

Grades--------Top 3------Bottom 3
55-----------------------------55
60-------------------------------
70-------------------------------
80---------------80--------------
80---------------80--------------
90---------------90--------------
55-----------------------------55
40-----------------------------40
70-------------------------------
85---------------85--------------
60-------------------------------
30-----------------------------30

 

[r937]

caution: untested ;-)

select grade
     , case when 3 >=
        ( select count(distinct grade)
            from grades
           where grade >= this.grade )
            then grade
            else null end  as Top3
     , case when 3 >=
        ( select count(distinct grade)
            from grades
           where grade <= this.grade )
            then grade
            else null end  as Bottom3
  from grades as this

r937.com | rudy.ca