# How to create intervals of 100 based on a column in a query?

I have a table with below structure:

``````create table item_test
(item_id       varchar2(10),
item_row      number)
``````

Sample data is like below , please note that numbers in `Item_Row` column starts at 1 and increases by 1 to a number without any gaps. What I need to do is to create intervals of 100 `([1-100] , [101-200] , [201-300] ,......)` for each `Item_Id`.

``````   Item_Id       Item_Row
------------   -----------
A               1
A               2
A              ...
A              ...
A              236  /* Item_Row starts from 1 and continues to 236 */

B               1
B              ...
B              ...
B              173  /* Item_Row starts from 1 and continues to 173 */

C               1
C               2
C              ...
C              ...
C              300  /* Item_Row starts from 1 and continues to 300 */
``````

The result will be like below:

``````  Item_Id       RowNum_From     RowNum_From
------------   -----------     -----------
A              1               100
A             101              200
A             201              236

B              1               100
B             101              173

C              1               100
C             101              200
C             201              300
``````

Is there any specific function for this?

The trick is to use the `trunc` function.

Here's a solution using pure SQL:

``````create table item_test
(item_id       varchar2(10),
item_row      number);

insert into item_test
select 'A', level from dual connect by level <= 236
union all
select 'B', level from dual connect by level <= 173
union all
select 'C', level from dual connect by level <= 300;
commit;

select item_id, min(item_row) as item_row_from, max(item_row) as item_row_to
from item_test
group by item_id, trunc(item_row-1, -2)
order by item_id, trunc(item_row-1, -2);
``````

And the result:

``````ITEM_ID    ITEM_ROW_FROM ITEM_ROW_TO
---------- ------------- -----------
A                      1         100
A                    101         200
A                    201         236
B                      1         100
B                    101         173
C                      1         100
C                    101         200
C                    201         300

8 rows selected.
``````

Really nice question - thought provoking - +1!

To solve this, I did the following (all the code below is available on the fiddle here):

• I borrowed the setup code from the accepted answer here.

``````insert into test
select 'A', level from dual connect by level <= 25
union all
select 'B', level from dual connect by level <= 18
UNION ALL
SELECT 'C', LEVEL FROM DUAL CONNECT BY LEVEL <= 25;
``````
• You really should be providing fiddles with sample data - you have > 1,300 points on dba.se so you should know this! It's a courtesy to those trying to help - it eliminates duplication of effort and provides a single point of truth for the question and would have eliminated the problem I had.

``````SELECT
c, fval1, lval2
FROM
(
SELECT
c, i,

FIRST_VALUE(i) OVER (PARTITION BY c, TRUNC((i - 1) / 10)
ORDER BY c, i ASC) AS fval1,

-- the default frame clause for window functions

FIRST_VALUE(i) OVER (PARTITION BY c, TRUNC((i - 1) / 10)  ORDER BY c, i ASC
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)  AS fval2,

-- the default doesn't work for the LAST_VALUE() fuction - you have to do it
-- this way.

LAST_VALUE(i) OVER (PARTITION BY c, TRUNC((i - 1) / 10)  ORDER BY c, i ASC
RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)  AS lval2

FROM
test
) TAB
GROUP BY c, fval1, lval2
ORDER BY c, fval1;
``````

Result:

``````C   FVAL1   LVAL2
A       1      10
A      11      20
A      21      25
B       1      10
B      11      18
C       1      10
C      11      20
C      21      25
``````

The interest here is that this could potentially provide more scope for analysis - with different parameters and/or different window functions.

Change the `10` in `TRUNC((i - 1) / 10` to a `7` and you can do a weekly analysis for example - see the last snippet here.