Select top 15 records from each group

Question

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);

Answer 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.