CODEWITHSUNDEEP
sqloracle-database
Amit
Amit

Reputation: 153

Create group numbers for rows from a table

I have a table of 6000 records, first 100 records should be batch-1, 2nd 100 should be batch-2 and so on. Kindly help me with the SQL query for the below question.

Below is the example of how I have wanted the data

Input 

data
1
2
3
4
5
6
7
8
9
10
11
12

I want output as

data  batch
1     Batch-1
2     Batch-1  
3     Batch-1
4     Batch-1
5     Batch-2
6     Batch-2
7     Batch-2
8     Batch-2
9     Batch-3
10    Batch-3
11    Batch-3
12    Batch-3

Upvotes: 1

Views: 221

Answers (1)

MT0
MT0

Reputation: 167972

You can use the ROW_NUMBER analytic function to get batches of 100 items:

SELECT t.*,
       'Batch-' || CEIL( ROW_NUMBER() OVER ( ORDER BY data ) / 100 ) AS batch
FROM   table_name t;

or, if data is consecutive then:

SELECT t.*,
       'Batch-' || CEIL( data / 100 ) AS batch
FROM   table_name t;

Upvotes: 5

Related Questions