CODEWITHSUNDEEP
sqlpostgresqlrepeat
iskandarblue
iskandarblue

Reputation: 7526

Repeating n row numbers in SQL

In postgresql syntax, row_number() can be used to generate incremental integers as in the example below. How would one repeat these integers n times or until the row length runs out? For example, using the following syntax, if the goal is to repeat each incremental integer 5 times, how could one generate the following output for rownum_repeat ? :

CREATE TABLE public.examples (
    id SERIAL PRIMARY KEY,
    text VARCHAR(200) NOT NULL
);

INSERT INTO
    public.examples (text)
VALUES
    ('val1'), ('val2'), ('val3'),  ('val4'), ('val5'), ('val6'), ('val7');

SELECT
    *
,ROW_NUMBER() OVER () AS rownum
FROM
    public.examples;


id  text    rownum rownum_repeat
1   "val1"  1      1
2   "val2"  2      1
3   "val3"  3      1
4   "val4"  4      1
5   "val5"  5      1
6   "val6"  6      2
7   "val7"  7      2

Upvotes: 0

Views: 137

Answers (1)

klin
klin

Reputation: 121604

Use the row number in the simple arithmetic:

SELECT
    *,
    ROW_NUMBER() OVER (ORDER BY id) AS rownum,
    (ROW_NUMBER() OVER (ORDER BY id)- 1) / 5+ 1 as rownum_repeat
FROM
    public.examples
ORDER BY id;

Db<>fiddle.

Note that row numbers depend on sort order.

Upvotes: 1

Related Questions