CODEWITHSUNDEEP
postgresql
Majid Darabi
Majid Darabi

Reputation: 741

Postgresql create fixed size groups of rows

We have a table of items that each item has an invoice id. We process this data in chunks based on invoice id (100 "invoices" at a time). Can you assist in creating a query that will assign a group id to each set of 100 invoices (chunk). Here's a logical example of what we wish to attain:

In this scenario, we know we have 9 rows and 5 invoices in advance. We want to create groups that each group contains 2 invoices except the last group.

enter image description here

Upvotes: 0

Views: 313

Answers (1)

Alexandros
Alexandros

Reputation: 2200

SELECT n1.*,
       n2.r
FROM dbtable n1,
  -- Group distinct inv_ids per group of 2
  -- or any other number by changing the /2 to e.g., /4     
  (SELECT inv_id,
          ((row_number() OVER())-1)/2 AS r
   FROM
     -- Get distinct inv_ids 
     (SELECT DISTINCT inv_id AS inv_id
      FROM dbtable
      ORDER BY inv_id) n2a) n2
WHERE n1.inv_id=n2.inv_id ;

This query has the advantage that will select correct groups of inv_ids even when the inv_ids are not consecutive.

SQL fiddle here

Upvotes: 1

Related Questions