SQL Query to fetch 300k latest records from 10 different groups equally

Question

I have a table which may contain around 3 million data, I need query to fetch around 300k data with status 'inactive' say all regions having 30k data and export into csv file.

select Id, Name, region, status, creationdate 
from Organization 
where status = 'inactive' 
group by region
having count = 30000
order by creationdate desc;

Does this query fetch the latest data in all regions or need to be fine tuned?

The query will not run you will get `ORA-00979: not a GROUP BY expression` — MT0, Jun 22 '22 at 07:56
Does this answer your question? [Select First Row of Every Group in sql](https://stackoverflow.com/questions/16529701/select-first-row-of-every-group-in-sql) — astentx, Jun 22 '22 at 10:43

score 4 · Answer 1 · edited Jun 22 '22 at 07:57

4

You can try this one:

SELECT * 
  FROM (select Id, Name, region, status, creationdate,
             ROW_NUMBER() OVER (PARTITION BY REGION ORDER BY creationdate DESC) as ROW_NUM 
        from Organization 
       where status='inactive')
WHERE ROW_NUM <= 30000

edited Jun 22 '22 at 07:57

MT0

143,790
11
59
117

answered Jun 22 '22 at 07:56

HRK

336
1
2
8

I have run query and getting same region data not distinct regions – kumar Jun 23 '22 at 13:08

SQL Query to fetch 300k latest records from 10 different groups equally

1 Answers1