Below are different combinations I have tried to solve for pattern generating based on previous solutions to similar questions.

1. Here is an example of a similar question and answers to it, where the person wanted the result set incremented by 1 every 10 records

SQL - How can I Increment a field in a results set by 1 for every 10 records returned

2. Another question which is similar, where user has described a solution to increment a sequence every 3 rows.

Generate Row Number for every 3 rows

3. Another one to Increment 150 rows,

4. Another solution to increment every 3 rows:

Generate a sequence number for every 3 rows in SQL

Question: I want to increment the count every 7 rows, have tried the above techniques in the code shown below, but it doesnt seem to work.

select pk,
    row_number() over (order by pk) as r,
    (row_number() over (order by pk) -1) /7 as r2,
    (row_number() over (order by pk) -1) /7 +1 as r3,
    (row_number() over (order by pk) -1) %7 +1 as r4,
    (row_number() over (order by pk) -1) %7 as r5,
    (row_number() over (order by pk) ) %7 +1 as r6
from (
    select 1 as pk
    union
    select 2 as pk
    union 
    select 3 as pk
    union
    select 4 as pk
    union
    select 5 as pk
    union 
    select 6 as pk
    union
    select 7 as pk
    union
    select 8 as pk
    union 
    select 9 as pk
) a;

DBFIDDLE ATTACHED:

Expected Results:

Below are different combinations I have tried to solve for pattern generating based on previous solutions to similar questions.

1. Here is an example of a similar question and answers to it, where the person wanted the result set incremented by 1 every 10 records

SQL - How can I Increment a field in a results set by 1 for every 10 records returned

2. Another question which is similar, where user has described a solution to increment a sequence every 3 rows.

Generate Row Number for every 3 rows

3. Another one to Increment 150 rows,

https://dba.stackexchange.com/questions/191817/incrementing-an-integer-every-150-rows

4. Another solution to increment every 3 rows:

Generate a sequence number for every 3 rows in SQL

Question: I want to increment the count every 7 rows, have tried the above techniques in the code shown below, but it doesnt seem to work.

select pk,
    row_number() over (order by pk) as r,
    (row_number() over (order by pk) -1) /7 as r2,
    (row_number() over (order by pk) -1) /7 +1 as r3,
    (row_number() over (order by pk) -1) %7 +1 as r4,
    (row_number() over (order by pk) -1) %7 as r5,
    (row_number() over (order by pk) ) %7 +1 as r6
from (
    select 1 as pk
    union
    select 2 as pk
    union 
    select 3 as pk
    union
    select 4 as pk
    union
    select 5 as pk
    union 
    select 6 as pk
    union
    select 7 as pk
    union
    select 8 as pk
    union 
    select 9 as pk
) a;

DBFIDDLE ATTACHED:

https://dbfiddle.uk/NcA1EIEH

Expected Results:

• sql
• snowflake-cloud-data-platform

• Please tag your RDBMS as they all have their own SQL dialects – Dale K Commented Feb 5 at 7:07

1 Answer 1

You don't have to use modular division for this. It'll give you the remainder. Instead you could just divide by 7 and then round up:

select pk, ceiling(pk / 7)::int as r1
from (
select 1 as pk
union
select 2 as pk
union 
select 3 as pk
union
select 4 as pk
union
select 5 as pk
union 
select 6 as pk
union
select 7 as pk
union
select 8 as pk
union 
select 9 as pk) a
order by pk -- optional
;

Results:

(Btw, you could write a recursive CTE to generate a sequence of numbers to avoid writing all the unions):

WITH RECURSIVE numbers(pk) AS (
      SELECT 1
      UNION ALL
      SELECT pk + 1 FROM numbers WHERE pk < 9
  )
SELECT pk, CEILING(pk / 7)::INT AS r1 
FROM numbers;