Quote:
Originally posted by oflowers
I'm having problems with the one homework problem, i'm a beginner and i'm pretty much trying to teach myself PL/SQL...can someone please help me out?...
scrore is a column of table students and its datatype is NUMBER. Assume that all values in this column score are distinct and that there are more than 3 values in this column. Write a piece of PL/SQL code that will find out the third largest score and the third smallest score. (You can print out the values or return the values.)
O~
|
You could consider using RANK or DENSE_RANK:
Code:
SQL> select ename, sal,
2 rank() over (order by sal) rank_asc,
3 rank() over (order by sal desc) rank_desc,
4 dense_rank() over (order by sal) dense_rank_asc,
5 dense_rank() over (order by sal desc) dense_rank_desc
6 from emp
7* order by sal;
ENAME SAL RANK_ASC RANK_DESC DENSE_RANK_ASC DENSE_RANK_DESC
---------- ---------- ---------- ---------- -------------- ---------------
SMITH 800 1 14 1 12
JAMES 950 2 13 2 11
ADAMS 1100 3 12 3 10
WARD 1250 4 10 4 9
MARTIN 1250 4 10 4 9
MILLER 1300 6 9 5 8
TURNER 1500 7 8 6 7
ALLEN 1600 8 7 7 6
CLARK 2450 9 6 8 5
BLAKE 2850 10 5 9 4
JONES 2975 11 4 10 3
SCOTT 3000 12 2 11 2
FORD 3000 12 2 11 2
KING 5000 14 1 12 1
14 rows selected.
Note there is no RANK_DESC=3, so probably DENSE_RANK suits you better:
Code:
SQL> select ename, sal
2 from
3 (
4 select ename, sal,
5 dense_rank() over (order by sal) dense_rank_asc,
6 dense_rank() over (order by sal desc) dense_rank_desc
7 from emp
9 )
10 where dense_rank_asc=3
11* or dense_rank_desc=3;
ENAME SAL
---------- ----------
JONES 2975
ADAMS 1100
Either print out or return those values as you prefer.
dBforums
>
PL/SQL Problem
07-03-03, 13:06
Linear Mode
