sqlpaginationfilteringgreatest-n-per-grouporacle-database

Select top 15 records from each group


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

Solution

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