Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2003
    Posts
    65

    Question Unanswered: top 10 using select or pl/sql

    Hi,
    I'm having a little problem i can't seem to solve. Here goes:
    I want to select the top 10 or top 15 from a table that has account
    a with multiply dollar values. For example
    a1..30
    a1..20
    a1..100
    a1..5
    a1..99
    b1..11
    b2..1000
    b3..3

    and so on but each account can have like 30-40.
    So i want to just select the top 10 for each acount and display it. And/Or put it into another
    table.
    I have tried selects and using a cursor but i just cant seem to get the logic worked out. I
    keep getting the top 10 but not for each account.
    So is anybody out there that can show me the way or if at all possible a solution.
    Thanks in advance.

    Your Confused Developer,
    LcO

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

    Re: top 10 using select or pl/sql

    select account, amount from
    (
    select account, amount, rank() over (partition by account order by amount desc) rankval
    from mytable
    )
    where rankval <= 10;

    You may want to use DENSE_RANK or ROW_NUMBER instead of RANK, depending on your needs. This shows the difference:

    SQL> select deptno, ename, sal,
    2 rank() over (partition by deptno order by sal desc) rank,
    3 dense_rank() over (partition by deptno order by sal desc) dense_rank,
    4 row_number() over (partition by deptno order by sal desc) row_number
    5 from emp
    6 /

    DEPTNO ENAME SAL RANK DENSE_RANK ROW_NUMBER
    ---------- ---------- ---------- ---------- ---------- ----------
    10 KING 5000 1 1 1
    10 CLARK 2450 2 2 2
    10 MILLER 1300 3 3 3
    20 SCOTT 3000 1 1 1
    20 FORD 3000 1 1 2
    20 JONES 2975 3 2 3
    20 ADAMS 1100 4 3 4
    20 SMITH 800 5 4 5
    30 BLAKE 2850 1 1 1
    30 ALLEN 1600 2 2 2
    30 TURNER 1500 3 3 3
    30 WARD 1250 4 4 4
    30 MARTIN 1250 4 4 5
    30 JAMES 950 6 5 6

    Look at deptno 20. This has 2 employees who tie for highest salary.
    RANK makes them both #1 and then the next employee is #3.
    DENSE_RANK makes them both #1 and then the next employee is #2.
    ROW_NUMBER gives every row a unique number.

  3. #3
    Join Date
    Nov 2003
    Posts
    65

    ref:

    thanks so much for the help, but i had a question:

    can you tell me if rank is a package that comes with oracle 8i or something i have to get or download?
    because right now i tried that rank() and i get must be declared...
    unless im using it wrong.

    hope you can help.

  4. #4
    Join Date
    Oct 2002
    Location
    Plymouth UK
    Posts
    116
    Analytic functions were introduced in 8.1.6 and later. So if you have an early version these are not available.

Posting Permissions

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