I want to select 10 merchant accounts, pick top 15 transaction records for each merchant account with page size like 10*50 ?
I have this query which gives me the top records, where I need a fix to pick the "top 15 records for each merchant account id" and not just the top 150 records.
Any pointers, suggestions, code fixes are welcome !
SELECT * FROM (
SELECT account_id,transaction_id,ROWNUM RNUM
FROM transactions
WHERE status='P' AND ROWNUM < ( (p_page_number * p_page_size) + 1)
GROUP BY account_id,transaction_id, ROWNUM
ORDER BY account_id
) a
WHERE rnum >= ( ( (p_page_number - 1) * p_page_size) + 1);
This will give you the "top 15 records for each merchant account id":
SELECT *
FROM (
SELECT
account_id,
transaction_id,
ROW_NUMBER() OVER(
PARTITION BY account_id -- Break into groups of merchants
ORDER BY transaction_id -- Assign row number based on transaction, within merchants
) RowNum
FROM transactions
WHERE status='P'
) src
WHERE src.RowNum <= 15
ORDER BY account_id, transaction_id
I'm not quite sure as to how your p_page_number, p_page_size, and ROWNUM
parameters come into play.