1. Registered User
Join Date
Jul 2003
Posts
1

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~

2. Moderator.
Join Date
Sep 2002
Location
UK
Posts
5,171

## Re: PL/SQL Problem

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
Either print out or return those values as you prefer.

3. Registered User
Join Date
Jul 2003
Location
US
Posts
314

## Rank on orderid

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.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•