PL/SQL Problem

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~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:

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:

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.|||Hi,
It is a good solution using the dense_rank, but i presume if you need to have more sorting on the same, you could do some sorting in the embedded query, like sorting on the ename itself.

I wish i saw this posting two weeks back, I could have saved quite a bit of time in a query, which wanted to look for the second orderid for a person out of around 10000 records.
:)