# Select repeating number x times and then increment

I need a select query that can generate `z` amount of rows starting from 1, repeating `x` times and then increment to the next number.

For example 10 rows repeating 4 times would result in the following:

``````| # |
|---|
| 1 |
| 1 |
| 1 |
| 1 |
| 2 |
| 2 |
| 2 |
| 2 |
| 3 |
| 3 |
``````

So z=10 and x=4 in the example given. These are parameters of a stored procedure. There are no limits or restrictions. The user can enter 2 billion if user needs 2 billion (unlikely).

``````DECLARE @z INT=10, @x INT=4;
WITH q AS (SELECT 0 AS n UNION ALL SELECT n+1 FROM q)
SELECT TOP(@z) n/@x+1 FROM q OPTION(MAXRECURSION 0);
``````
• Technically, `TOP` needs an `ORDER BY` clause in the same scope to guarantee correct results. Recursive CTEs are not the most efficient way to generate numbers on the fly (not a particular concern if `@z` is small enough). Aug 29 '18 at 17:19

# PostgreSQL

This can be easily done with PostgreSQL using `generate_series`

``````SELECT t.x
FROM generate_series(1,10) AS t(x)
CROSS JOIN LATERAL generate_series(1,4) AS g(x)
ORDER BY t.x;
``````

Here we generate a series `[1..10]` and then repeat it four times.

Output

`````` x
----
1
1
1
1
2
2
2
2
3
3
3
3
``````

One solution using simple integer arithmetic on a table of numbers. This example uses an efficient way to generate a sequence on the fly, in practice you would probably have a permanent Numbers table.

``````DECLARE
@z bigint = 10,
@x integer = 4;

-- Itzik Ben-Gan's row generator
WITH
L0   AS (SELECT 1 AS c UNION ALL SELECT 1),
L1   AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
L2   AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
L3   AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
L4   AS (SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
L5   AS (SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
Nums AS (SELECT ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) AS n FROM L5)
-- Main logic
SELECT TOP (@z)
# = ((N.n - 1) / @x) + 1
FROM Nums AS N
ORDER BY N.n;
``````

Output:

```╔═══╗
║ # ║
╠═══╣
║ 1 ║
║ 1 ║
║ 1 ║
║ 1 ║
║ 2 ║
║ 2 ║
║ 2 ║
║ 2 ║
║ 3 ║
║ 3 ║
╚═══╝
```

Execution plan: