SQL Oracle - 50 records for each agent for previous month

Question

I've a table (processed_amounts) which shows 'amount', 'date_processed', and the 'agent' which processed the amount. I need a query which will pull through a specified number of processed entries, for instance, 50 processed entries, for a specified period, for instance, the previous month, for a list of several agents. Is there a way of writing this all in one query, as I'll be running this monthly? So say I needed a report of 50 entries each for 10 different agents, which were processed during the month of May 2018, could I fit this into one query?

So, for example, the table looks below;

Date_Processed │ Amount │ Agent   
01/05/2018     │ £35:90 │ tbrown  
25/05/2018     │ £79:00 │ asmith

My query would need to pull through 50 entries for both tbrown and asmith for May 2018.

Many thanks, for your help

Please **[edit]** your question and add some [sample data](http://plaintexttools.github.io/plain-text-table/) and the expected output based on that data. [Formatted text](http://stackoverflow.com/help/formatting) please, [no screen shots](http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557). ([edit] your question - do **not** post code or additional information in comments) — , Jun 12 '18 at 10:16

score 0 · Accepted Answer · answered Jun 12 '18 at 10:30

If I understand correctly, you can use row_number():

select pa.*
from (select pa.*,
             row_number() over (partition by agent order by ?) as seqnum
      from processed_amounts pa
      where agent in ( . . . ) and -- list of agents here
            date_processed >= '2018-05-01' and
            date_processed < '2018-06-01'
     ) pa
where seqnum <= 50;

The ? is for the column that determines which 50 you want -- the first in the month, the last, arbitrary 50, random 50 and so on. If you don't care which 50, you can just put in agent for an arbitrary 50 rows.

SQL Oracle - 50 records for each agent for previous month

1 Answers1