asked on

PL SQL select top 20 transactions per account from list of accounts

I have an Oracle8 database with tables Accounts, Account_transactions and Account_range.

The Account_range tabel has 40 random account numbers.

How do I select the top 20 transactions per account from the Account_range ?

Table summary:
Accounts.Account_no
Accounts.Account_name .....etc

Account_transactions.Account_no
Account_transactions.Trans_no.....etc

Account_range. Account_no

Sean Stuber

what defines an transactions as "top" vs "bottom" how are your sorting?
what are you sorting? trans_no?

"top" means 1,2,3,...,19,20 ? smallest numbers first?
"top" means 999, 998,997,...981,980? largest numbers first?

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

HainKurt

you can use similar query here

replace the table names with yours, and the condition with yours, ie: ar.account_no = at.account_no

where

r : order of transaction in an account
r2 : order of account
r1 : order of transaction (not important)

SELECT   *
    FROM (SELECT r2, at.*, COUNT (*) OVER (PARTITION BY r2 ORDER BY r2, r1) r
            FROM (SELECT ROWNUM r1, at.* FROM account_transactions at) at,
                 (SELECT ROWNUM r2, ar.*  FROM account_range ar) ar
           WHERE at.account_no = ar.account_no /*add your order here: order by trans_date desc */)
   WHERE r <= 10
ORDER BY r2, r

Open in new window

finder08

ASKER

perfect....problem sorted....thank you

Sean Stuber

glad I could help