In a table I have 300 records of which I am going to use those rows that have been modified with today's date. Through a query I obtain a Minimum ID and a Maximum ID For example, if I modified rows between ids 1 and 100, the query would return: min: 1 max: 100 but this does not mean that it modifies all 100 rows, among those values it may modify only 37 rows. What I need to know is how to be able to divide the total number of rows into groups of 10 rows and how to know the order of the Ids even if they are not consecutive. For example, if we say that there are 37 rows that have been modified, speaking of groups of 10 rows, an example would be as follows:
row_group_range | id_values | row_group
------------------------------------------------------------------------------------
10 |1,3,5,6,7,10,14,16,20,22 | 1
10 |26,30,33,36,41,44,48,52,60,61 | 2
10 |65,68,70,72,75,77,79,80,84,85 | 3
7 |90,91,92,93,94,95,96,98 | 4
If you have a table (or query) which gives you the modified id values with one value per row and you want to display the values in groups of 10.
For example, if you have your sample data as a column of id_value:
CREATE TABLE table_name (id_value) AS
SELECT COLUMN_VALUE
FROM TABLE(SYS.ODCINUMBERLIST(
1,3,5,6,7,10,14,16,20,22,
26,30,33,36,41,44,48,52,60,61,
65,68,70,72,75,77,79,80,84,85,
90,91,92,93,94,95,96,98
));
Then you can use the ROW_NUMBER analytic function to number the rows and split them into groups of 10 and then you can aggregate:
SELECT COUNT(*) AS row_group_range,
LISTAGG(id_value, ',') WITHIN GROUP (ORDER BY id_value) AS id_values,
row_group
FROM (
SELECT id_value,
CEIL(ROW_NUMBER() OVER (ORDER BY id_value)/10) AS row_group
FROM table_name
)
GROUP BY row_group
Which outputs:
| ROW_GROUP_RANGE | ID_VALUES | ROW_GROUP |
|---|---|---|
| 10 | 1,3,5,6,7,10,14,16,20,22 | 1 |
| 10 | 26,30,33,36,41,44,48,52,60,61 | 2 |
| 10 | 65,68,70,72,75,77,79,80,84,85 | 3 |
| 8 | 90,91,92,93,94,95,96,98 | 4 |
Note: your sample data has 38 values, not 37.