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

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.

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.

Analytic functions were introduced in 8.1.6 and later. So if you have an early version these are not available.