Home » SQL & PL/SQL » SQL & PL/SQL » Question about getting the Top N or Bottom N of a dataset
Question about getting the Top N or Bottom N of a dataset [message #192321] Mon, 11 September 2006 18:15 Go to next message
Rustican
Messages: 51
Registered: July 2006
Member
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

[Updated on: Mon, 11 September 2006 18:18]

Report message to a moderator

Re: Question about getting the Top N or Bottom N of a dataset [message #192327 is a reply to message #192321] Mon, 11 September 2006 19:16 Go to previous message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
sql>with top3 as (select sal
  2                  from (select sal, dense_rank() over (order by sal desc) rnk
  3                          from emp)
  4                 where rnk <= 3),
  5       bot3 as (select sal 
  6                  from (select sal, dense_rank() over (order by sal) rnk
  7                          from emp)
  8                 where rnk <= 3)
  9  select sal,
 10         (select e.sal from dual where e.sal in (select * from top3)) top3,
 11         (select e.sal from dual where e.sal in (select * from bot3)) bottom3
 12    from emp e;

      SAL      TOP3   BOTTOM3
--------- --------- ---------
      800                 800
     1600
     1250
     2975      2975
     1250
     2850
     2450
     3000      3000
     5000      5000
     1500
     1100                1100
      950                 950
     3000      3000
     1300

14 rows selected.
Previous Topic: Not able to retrieve unicode characters on the client.
Next Topic: ROWS to COLS
Goto Forum:
  


Current Time: Sat Dec 03 10:19:45 CST 2016

Total time taken to generate the page: 0.07908 seconds