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

Thanks Balaji Birajdar. I wondered what version of the database are you using? — alexgibbs, Oct 07 '19 at 18:08
Lookup cross apply and `fetch first 15 rows only`.. https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:9530807800346558418 — xQbert, Oct 07 '19 at 19:21
What 15 transactions? How do you want them ordered the most recent? the oldest? based on transaction_ID? — xQbert, Oct 07 '19 at 20:02

The Impaler · Answer 1 · 2019-10-07T18:39:10.273

2

You can use the DENSE_RANK() window function to assign group numbers to rows, and ROW_NUMBER() to assign a serial number within each group. Then, filtering is easy.

For example:

select *
from (
  select
    account_id, 
    transaction_id,
    dense_rank() over(order by account_id) as g,
    row_number() over(partition by account_id order by transaction_id) as rn
  from transactions
  where status = 'P'
) x
where g <= 10 -- the first 10 groups (accounts)
  and rn <= 15 -- the first 15 transactions within each group

edited Oct 07 '19 at 18:39

answered Oct 07 '19 at 13:06

The Impaler

45,731
9
39
76

1

Not important, but I noticed the main select is missing any attributes. Thanks – alexgibbs Oct 07 '19 at 18:25
1

@alexgibbs Thanks. Totally missed it. – The Impaler Oct 07 '19 at 18:39
This is perfect. Thank you very much @TheImpaler You saved my day. – Balaji Birajdar Oct 09 '19 at 13:41

score 1 · Accepted Answer · answered Oct 07 '19 at 13:05

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.

Select top 15 records from each group

2 Answers2

Related