# 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
###### Who is Participating?

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,
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

Commented:
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

Sr. 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
``````
0

Author Commented:
perfect....problem sorted....thank you
0

Commented: