SQL: group by number of rows (divide result into sections with particular number of rows)

SQL: group by number of rows

Oracle

Example shows how to divide "group by clause"result into sections with particular number of rows.

Table:
select column1 from table1;

Query:

Group capacity: 4

with query1 as
    (
    select 
    column1,
    row_number()  OVER (PARTITION BY column1  ORDER BY column1)-1 as c1
    from table1
    )
    select 
    column1,
    count(column1)
    from query1
    group by column1, floor(c1/4)
    order by column1, count(column1) desc

Result: