Search code examples
sqlt-sqlrownum

SQL query to get start and end positions based on batch size

I have a table A with identity column as PK, along with other columns. This table has like 10M records. Sample data in identify column is as below

ID
--
1
2
3
5
6
7
8
9
10

Note: there is no 4. it would have got delete.

i want to process them in batches and track if the processing is complete or not. so i want to create another table with below structure

batch id, start Id, end Id

in that in want data like

batch id, start Id, end Id
1,1, 2  
2,3,5
3,6,7
4,8,9
5,10,10

This is considering batch size of 2. Batch size is configurable.

What query should i write to form above table by querying table A.

Thanks in Advance.

Solution

  • You can use row_number() and arithmetic:

    select floor((seqnum + 1) / 2) as batch, min(id), max(id)
from (select t.*, row_number() over (order by id) as seqnum
      from t
     ) t
group by floor((seqnum + 1) / 2)