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
finder08Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
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?
0
sdstuberCommented:
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,
               a.account_name,
               at.trans_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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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

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

0
finder08Author Commented:
perfect....problem sorted....thank you
0
sdstuberCommented:
glad I could help
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.