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_name .....etc


Account_range. Account_no
Who is Participating?
sdstuberConnect With a Mentor Commented:
assuming you mean sort by trans_no and you mean "top" as smallest numbers first then try this...

SELECT account_no, account_name, trans_no
FROM   (SELECT ar.account_no,
               ROW_NUMBER() OVER (PARTITION BY ar.account_no ORDER BY at.trans_no ASC) rn
        FROM   account_range ar, account_transactions at, accounts a
        WHERE  ar.account_no = a.account_no AND ar.account_no = at.account_no)
WHERE  rn <= 20
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?
HainKurtSr. System AnalystCommented:
you can use similar query here

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


r : order of transaction in an account
r2 : order of account
r1 : order of transaction (not important)
    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

finder08Author Commented:
perfect....problem sorted....thank you
glad I could help
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.