Results 1 to 3 of 3

Thread: PL/SQL Problem

  1. #1
    Join Date
    Jul 2003
    Posts
    1

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

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

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

  3. #3
    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
  •