# How to generate a 1, 2, 3, 3, 2, 1, 1, 2, 3, 3, 2, 1, ... series in standard SQL or T-SQL?

Given two numbers `n` and `m`, I want to generate a series of the form

``````1, 2, ..., (n-1), n, n, (n-1), ... 2, 1
``````

and repeat it `m` times.

For instance, for `n = 3` and `m = 4`, I want a sequence of the following 24 numbers:

``````1, 2, 3, 3, 2, 1, 1, 2, 3, 3, 2, 1, 1, 2, 3, 3, 2, 1, 1, 2, 3, 3, 2, 1
----------------  ----------------  ----------------  ----------------
``````

I know how to achieve this result in PostgreSQL by either of two methods:

Using the following query, which uses the `generate_series` function, and a few tricks to guarantee that the order is the right one:

``````WITH parameters (n, m) AS
(
VALUES (3, 5)
)
SELECT
xi
FROM
(
SELECT
i, i AS xi
FROM
parameters, generate_series(1, parameters.n) AS x(i)
UNION ALL
SELECT
i + parameters.n, parameters.n + 1 - i AS xi
FROM
parameters, generate_series(1, parameters.n) AS x(i)
) AS s0
CROSS JOIN
generate_series (1, (SELECT m FROM parameters)) AS x(j)
ORDER BY
j, i ;
``````

... or use a function for the same purpose, with adjoint and nested loops:

``````CREATE FUNCTION generate_up_down_series(
_elements    /* n */ integer,
_repetitions /* m */ integer)
RETURNS SETOF integer AS
\$BODY\$
declare
j INTEGER ;
i INTEGER ;
begin
for j in 1 .. _repetitions loop
for i in         1 .. _elements loop
return next i ;
end loop ;
for i in reverse _elements .. 1 loop
return next i ;
end loop ;
end loop ;
end ;
\$BODY\$
LANGUAGE plpgsql IMMUTABLE STRICT ;
``````

How could I possibly do the equivalent in either standard SQL or in Transact-SQL / SQL Server?

## TL;DR

This is a long one, so I'm putting the best (i.e. fastest of my) methods here first. It makes use of the INTARRAY extension - for parameters of 340 and 570, it takes 21ms*. The second best (22 ms - same parameters) only uses standard PostgreSQL constructs. If anyone else comes up with (a) faster method(s), I will put them here and "retire" mine!

* 8 GB RAM, Intel i5 11th gen CPU, Quad core - 8 threads, NVMe 256 GB drive

## Introduction:

This question intrigued me (+1) and I pondered

• a) how to answer it and

• b) how could the answer be generalised?

All of the code below can be found in the various fiddles - per method/contributor.

Interestingly, nobody who's answered so far (10 answers - and some very good quality SQL/programming to boot!) has made use of `ARRAY`s (tutorial), which I believe are very helpful in this case.

In a general sense, my approach has been to generate the first series as an ARRAY (`{1, 2,.. n, n,..2, 1}`) and then generate `m` of these for the complete task.

I took three five approaches:

• the first is PostgreSQL specific, making use of `GENERATE_SERIES()` (tutorial) and `ARRAY`s. There's also a function call that can be replaced with a `RECURSIVE CTE` (`"RCTE"` - see tutorial).

• the second (also PostgreSQL specific) combines an `RCTE` with a call to `GENERATE_SERIES()` and `ARRAY`s. The `GENERATE_SERIES()` can be replaced with an `RCTE` - see solution 3.

• the third solution is `"Pure SQL"` and should work on any RDBMS that supports `RCTE`s (SQL Server for example) - can also use a `numbers` table (i.e. a sequence) Following discussions on the dba chatroom, I have removed the requirement for `RCTE`s to be used to construct sequences.

• then there are two "speedy" solutions which rely on the fact that `UNNEST()` preserves order.

## Preparation step:

I used a table called `param` to store the values (`3` & `5`) as per the question - these can obviously be changed. Also, for the benchmarking steps (see below), I used this param table for all queries tested so that there would be an even playing field. As mentioned above, a `numbers` sequence table is also permitted.

``````--
-- Setup of parameters...
--

CREATE TABLE param(n INT, m INT);
INSERT INTO param (VALUES(3, 5));
SELECT * FROM param;

--
-- Setup of numbers
--

CREATE TABLE numbers (n INT);
INSERT INTO numbers
SELECT GENERATE_SERIES(1, ((SELECT m FROM param)));
SELECT * FROM numbers LIMIT 5;
``````

The first method is as follows:

# Method 1 - GENERATE_SERIES (fiddle):

## Step 1:

``````--
-- Step 1
--
SELECT
GENERATE_SERIES(1, (SELECT n FROM param)) AS the_first_series
UNION ALL
SELECT
GENERATE_SERIES((SELECT n FROM param), 1, -1);
``````

Result:

``````the_first_series
1
2
3
3
2
1
``````

## Step 2:

``````--
--  Two possible Step 2s using a PL/pgSQL function or an RCTE
--
CREATE OR REPLACE FUNCTION fill_array_with_seq(the_array anyarray, seq_num INT)
RETURNS ANYARRAY LANGUAGE PLpgSQL AS \$\$
DECLARE
BEGIN
FOR i IN 1..seq_num LOOP
the_array[i] := i;
i = i + 1;
END LOOP;
RETURN the_array;
end \$\$;

SELECT fill_array_with_seq(ARRAY[]::INT[], (SELECT n * 2 FROM param));

WITH RECURSIVE cte_fill_arr (n, f_arr) AS
(
SELECT 1 AS n, ARRAY[1]::INT[] AS f_arr
UNION ALL
SELECT n + 1, array_append(f_arr, n + 1)
FROM cte_fill_arr
WHERE n < (SELECT n * 2 FROM param)
)
SELECT f_arr FROM cte_fill_arr
WHERE CARDINALITY(f_arr) = (SELECT n * 2 FROM param);
``````

Results (same):

``````fill_array_with_seq
{1,2,3,4,5,6}
f_arr
{1,2,3,4,5,6}
``````

## Step 3:

``````--
-- Step 3
--

WITH RECURSIVE cte_fill_arr (n, f_arr) AS
(
SELECT 1 AS n, ARRAY[1]::INT[] AS f_arr
UNION ALL
SELECT n + 1, array_append(f_arr, n + 1)
FROM cte_fill_arr
WHERE n < (SELECT n * 2 FROM param)
)
SELECT
ROW_NUMBER() OVER () AS rn,
(
SELECT f_arr FROM cte_fill_arr
WHERE CARDINALITY(f_arr) = (SELECT n * 2 FROM param)
),

-- could use
--
-- fill_array_with_seq(ARRAY[]::INT[], (SELECT n * 2 FROM param))
--

ARRAY
(
SELECT
GENERATE_SERIES(1, (SELECT n FROM param))
UNION ALL
SELECT
GENERATE_SERIES((SELECT n FROM param), 1, -1)
) AS arr
FROM
GENERATE_SERIES(1, (SELECT m FROM param)) AS x;
``````

Result:

``````rn         f_arr             arr
1  {1,2,3,4,5,6}   {1,2,3,3,2,1}
2  {1,2,3,4,5,6}   {1,2,3,3,2,1}
3  {1,2,3,4,5,6}   {1,2,3,3,2,1}
4  {1,2,3,4,5,6}   {1,2,3,3,2,1}
5  {1,2,3,4,5,6}   {1,2,3,3,2,1}
``````

Finally:

``````--
--  Steps 4 & 5 - separate subquery not shown
--

WITH RECURSIVE cte_fill_arr (n, f_arr) AS
(
SELECT 1 AS n, ARRAY[1]::INT[] AS f_arr
UNION ALL
SELECT n + 1, array_append(f_arr, n + 1)
FROM cte_fill_arr
WHERE n < (SELECT n * 2 FROM param)
)
SELECT the_series FROM
(
SELECT
ROW_NUMBER() OVER () AS rn,
UNNEST
(
(
SELECT f_arr FROM cte_fill_arr
WHERE CARDINALITY(f_arr) = (SELECT n * 2 FROM param)
)
) AS seq,
UNNEST
(
ARRAY
(
SELECT
GENERATE_SERIES(1, (SELECT n FROM param))
UNION ALL
SELECT
GENERATE_SERIES((SELECT n FROM param), 1, -1)
)
) AS arr
FROM
GENERATE_SERIES(1, (SELECT m FROM param)) AS x
ORDER BY rn, seq
) AS fin_arr
ORDER BY rn, seq;
``````

Result:

``````the_series
1
2
3
3
2
1
1
2
...
... snipped for brevity
...
``````

# Method 2 - Recursive CTE (fiddle):

Here, I managed to "kill two birds with one stone" by constructing both the desired sequence and its numbering scheme in the same RCTE as follows:

``````--
-- Step 1
--
WITH RECURSIVE cte_fill_array AS -- (cnt, val_arr, cnt_arr) AS
(
SELECT 1 AS i, 1 AS cnt, ARRAY[1] AS val_arr, ARRAY[1] AS cnt_arr
UNION ALL
SELECT i + 1, cnt + 1,
ARRAY_APPEND
(
val_arr,
(
SELECT
CASE
WHEN cnt < (SELECT n FROM param) THEN (i + 1)
WHEN cnt = (SELECT n FROM param) THEN cnt
WHEN cnt > (SELECT n FROM param) THEN 6 - i
END
)
),
ARRAY_APPEND(cnt_arr, cnt + 1)
FROM cte_fill_array
WHERE cnt < 2 * (SELECT n FROM param)
)
SELECT i, cnt, val_arr, cnt_arr FROM cte_fill_array;
``````

Result:

``````i   cnt val_arr cnt_arr
1   1   {1} {1}
2   2   {1,2}   {1,2}
3   3   {1,2,3} {1,2,3}
4   4   {1,2,3,3}   {1,2,3,4}
5   5   {1,2,3,3,2} {1,2,3,4,5}
6   6   {1,2,3,3,2,1}   {1,2,3,4,5,6}
``````

We only want the last record, so we select this by using the `CARDINALITY()` function - where that is equal to (`n * 2`) is the last record (step not shown individually).

Final step - see fiddle for more detail

``````--
--  Steps 2 - end
--
WITH RECURSIVE cte_fill_arr (n, f_arr) AS
(
SELECT 1 AS n, ARRAY[1]::INT[] AS f_arr
UNION ALL
SELECT n + 1, array_append(f_arr, n + 1)
FROM cte_fill_arr
WHERE n < (SELECT n * 2 FROM param)
)
SELECT arr AS the_series FROM
(
SELECT
ROW_NUMBER() OVER () AS rn,
UNNEST
(
(
SELECT f_arr FROM cte_fill_arr
WHERE CARDINALITY(f_arr) = (SELECT n * 2 FROM param)
)
) AS seq,
UNNEST
(
ARRAY
(
SELECT
GENERATE_SERIES(1, (SELECT n FROM param))
UNION ALL
SELECT
GENERATE_SERIES((SELECT n FROM param), 1, -1)
)
) AS arr
FROM
GENERATE_SERIES(1, (SELECT m FROM param)) AS x
ORDER BY rn, seq
) AS fin_arr
ORDER BY rn, seq;
``````

Result (same as others):

``````the_series
1
2
3
3
...
... snipped for brevity
...
``````

A simpler, (and IMHO) more elegant solution exists - using the `GENERATE_SUBSCRIPTS()` function (explanation) as follows:

``````WITH RECURSIVE cte (i) AS
(
SELECT 1 AS i, 1 AS cnt
UNION ALL
SELECT
CASE
WHEN cnt < (SELECT n FROM param) THEN (i + 1)
WHEN cnt = (SELECT n FROM param) THEN cnt
ELSE i - 1
END AS i,
cnt + 1
FROM cte
WHERE cnt < 2 * (SELECT n FROM param)
)
SELECT the_arr
FROM
(
SELECT
x,
UNNEST(ARRAY(SELECT i FROM cte))    AS the_arr,
GENERATE_SUBSCRIPTS(ARRAY(SELECT i FROM cte), 1) AS ss

FROM GENERATE_SERIES(1, (SELECT m FROM param)) AS t(x)
) AS s
ORDER BY x, ss;
``````

Result (same):

``````the_series
1
2
3
3
...
... snipped for brevity
...
``````

# Method 3 - Pure SQL (fiddle):

## Final step:

Since all of the code below has been seen above in one form or another, I'm just including the final step. No PostgreSQL specific functionality has been used and it also works under SQL Server 2019 (Linux fiddle) - also works back to 2016 - all versions.

``````WITH RECURSIVE cte (i, cnt) AS
(
SELECT 1 AS i, 1 AS cnt
UNION ALL
SELECT
CASE
WHEN cnt < (SELECT n FROM param) THEN (i + 1)
WHEN cnt = (SELECT n FROM param) THEN cnt
ELSE i - 1
END AS i,
cnt + 1
FROM cte
WHERE cnt < 2 * (SELECT n FROM param)
)
SELECT
n.n, c.i, c.cnt
FROM
cte c
CROSS JOIN numbers n
ORDER BY n.n, c.cnt;
``````

Result (same):

``````i
1
2
3
3
``````

# 4th solution (and `clubhouse leader!`) (fiddle):

``````SELECT UNNEST(arr)
FROM
(
SELECT arr, GENERATE_SERIES(1, (SELECT m FROM param)) AS gs
FROM
(
SELECT
ARRAY
(
SELECT x
FROM GENERATE_SERIES(1, (SELECT n FROM param)) x
UNION ALL
SELECT x
FROM GENERATE_SERIES((SELECT n FROM param), 1, -1) x
) AS arr
) AS t
) AS s;
``````

Same result as for all the others.

# 5th solution (Honourable mention) (fiddle):

``````SELECT
UNNEST
(
ARRAY_CAT
(
ARRAY
(
SELECT GENERATE_SERIES(1, (SELECT n FROM param))::INT
),
ARRAY
(
SELECT GENERATE_SERIES((SELECT n FROM param), 1, -1)::INT
)
)
) FROM GENERATE_SERIES(1, (SELECT m FROM param));
``````

Same result as for all the others.

# 6th Solution (another scorcher! - uses the INTARRAY extension fiddle):

``````WITH cte AS
(
SELECT
ARRAY(SELECT GENERATE_SERIES(1, (SELECT n FROM param))) AS arr
)
SELECT UNNEST
(
(
SELECT ARRAY_CAT(c.arr, SORT(c.arr, 'DESC'))
FROM cte c
)
) FROM GENERATE_SERIES(1, (SELECT m FROM param));
``````

Same result!

# Benchmarking:

I benchmarked all of the PostgreSQL solutions.

I have done my utmost to be fair in these benchmarks - I used a param table `(3, 5)` (also `(34, 57)` and `(340, 570)` at `(home)`) in my SQL. For those queries requiring a `number` table (i.e. a sequence), after discussion, I have included it for those queries requiring it. I'm not entirely sure about this, since consultants are frequently forbidden from creating separate tables, no matter how trivial, but this appears to have been the consensus!

Please let me know if you are unhappy with any of the tests and I'll gladly rerun them!

I used `db<>fiddle` for the tests and the usual caveats apply - I don't know what else is running on that server at any given moment in time - I took an average of several runs for each solution (the vast bulk of the results were within ~ 10% of each other - discarded obvious outliers (longer, not shorter times).

It was pointed out to me (knew anyway) that 3 & 5 aren't very large numbers - I did try with low 100's for each parameter, but the runs kept failing on db<>fiddle.uk, but I would just say that the all of the runs were remarkably consistent with each other, only varying by ~ +- 10%.

The second reading runs with a are for values of 34 & 57 - feel free to try yourselves.

With the `(home)` tests, I used params of `(340, 570)` on an 8GB machine (i5 - 10th gen, NVMe 256GB) - nothing else running - variance v. low ~ 1/2%!

• `Vérace's (Another scorcher using INTARRAY!)` 6th solution (fiddle) (0.110ms)/0.630 ms/21.5 ms (home) - `new leader`!

• Vérace's (ex-Clubhouse leader) 4th solution (fiddle) 0.120 ms/0.625 ms/22.5 ms (home)

• Vérace's (Honourable mention) 5th solution (fiddle) (0.95ms/0.615 (goes down!)/26 ms (home)

• Vérace GENERATE_SERIES SQL method (fiddle): 0.195 ms/3.1 ms/140ms (home)

• Vérace RECURSIVE CTE SQL method (fiddle): 0.200 ms/2.9 ms/145m (home)

• Vérace GENERATE_SUBSCRIPTS() SQL method (fiddle): 0.110 ms/2.75 ms/130ms (home)

• Vérace "Pure SQL" method (fiddle): 0.134 ms/2.85ms/190ms (home)

• OP's SQL method (fiddle): 12.50 ms/18.5ms/190ms (home)

• OP's PL/pgSQL function method (fiddle): 0.60 ms/0.075ms/86ms (home)

• ypercube's SQL method (fiddle): 0.175 ms, /4.3 ms /240 ms (home)

• ypercube's alternative method (fiddle): 0.090 ms/0.95 ms/36ms (home)

• Erwin Brandtstetter's SQL method (fiddle): 2.15 ms /3.65 ms/160ms (home) (160 drops to ~ 100 without the ORDER BY - home)

• Erwin Brandtstetter's function method (fiddle): 0.169 ms/2.3 ms/180 ms (home)

• Abelisto's SQL method (fiddle) 0.145/fails if params changed?

• Evan Carroll's SQL method (fiddle) 0.125 ms/1.1ms/45ms (home)

• McNet's PL/pgSQL method (fiddle) 0.075 ms/ 0.075 ms/125ms (home)

Again, I reiterate (at the risk of repeating myself (multiple times! :-) )), if you are unhappy with your benchmark, please let me know and I will include any amendments here - I would just stress that I am genuinely interested in fairness and actually learning from this process - unicorn points are all very well, but my priority is on increasing my (our) knowledge-base!

The source code of PostgreSQL is a bit above my pay grade, but I believe that operations with `GENERATE_SERIES` and `ARRAY`s preserve order - the `WITH ORDINALITY` implies this (again, I think) - the correct answers come up even without paying attention to ordering (although this not a guarantee)! @ErwinBrandstetter says that:

• I added ORDER BY to guarantee the requested order. With current versions or Postgres it also works without ORDER BY for the simple query - but not necessarily in more complex queries! That's an implementation detail (and it's not going to change) but not mandated by the SQL standard.

My understanding is that `ARRAY`s are fast in PostgreSQL because much of the backend code is implemented through them - but as I said, I'm not really a `C` expert.

Current standings (as of 2021-10-27 13:50 UTC) are:

• Vérace 1st, 2nd & 3rd,
• ypercube 4th,
• Evan Carroll 5th
• the rest of the field...

I found these posts on ARRAYs from Erwin Brandstetter (1) & a_horse_with_no_name (2) very helpful! Other's that I found helpful were as follows (1, 2).

• `EXPLAIN (ANALYZE, BUFFERS, VERBOSE)` is a good way to get details for the query plan and execution. But it adds considerable overhead, which matters most for cheap operations. To measure raw performance you'd rather use `EXPLAIN (ANALYZE, TIMING OFF)` or some other method as listed here: stackoverflow.com/a/9065976/939860 I'd consider "4. Precise manual measurement with `clock_timestamp()`". Won't change the ranking much, just produce times closer to reality. Oct 27, 2021 at 20:18

## Postgres

You can make it work with a single `generate_series()` and basic math (see mathematical functions).

Wrapped into a simple SQL function:

``````CREATE OR REPLACE FUNCTION generate_up_down_series(n int, m int)
RETURNS SETOF int
LANGUAGE sql IMMUTABLE AS
\$func\$
SELECT CASE WHEN n2 < n THEN n2 + 1 ELSE n*2 - n2 END
FROM  (
SELECT n2m, n2m % (n*2) AS n2
FROM   generate_series(0, n*2*m - 1) n2m
) sub
ORDER  BY n2m
\$func\$;
``````

Call:

``````SELECT * FROM generate_up_down_series(3, 4);
``````

Generates the desired result. n and m can be any integer where n*2*m does not overflow `int4`.

### How?

In the subquery:

• Generate the desired total number of rows (n*2*m), with a simple ascending number. I name it `n2m`. 0 to N-1 (not 1 to N) to simplify the following modulo operation.

• Take it % n*2 (`%` is the modulo operator) to get a series of n ascending numbers, m times. I name it `n2`.

In the outer query:

• Add 1 to lower half (n2 < n).

• For the upper half (n2 >= n) mirror of the lower half with n*2 - n2.

• I added `ORDER BY` to guarantee the requested order. With current versions of Postgres it also works without `ORDER BY` for the simple query - but not necessarily in more complex queries! That's an implementation detail (and it's not going to change) but not mandated by the SQL standard.

Unfortunately, `generate_series()` is Postgres specific and not standard SQL, as has been commented. But we can reuse the same logic:

## Standard SQL

You can generate the serial numbers with a recursive CTE instead of `generate_series()`, or, more efficiently for repeated use, create a table with serial integer numbers once. Anyone can read, noone can write to it!

``````CREATE TABLE int_seq (i integer);

WITH RECURSIVE cte(i) AS (
SELECT 0
UNION ALL
SELECT i+1 FROM cte
WHERE  i < 20000  -- or as many you might need!
)
INSERT INTO int_seq
SELECT i FROM cte;
``````

Then, the above `SELECT` becomes even simpler:

``````SELECT CASE WHEN n2 < n THEN n2 + 1 ELSE n*2 - n2 END AS x
FROM  (
SELECT i, i % (n*2) AS n2
FROM   int_seq
WHERE  i < n*2*m  -- remember: 0 to N-1
) sub
ORDER  BY i;
``````

In Postgres, it's easy using the `generate_series()` function:

``````WITH
parameters (n, m) AS
( VALUES (3, 5) )
SELECT
CASE WHEN g2.i = 1 THEN gn.i ELSE p.n + 1 - gn.i END AS xi
FROM
parameters AS p,
generate_series(1, p.n) AS gn (i),
generate_series(1, 2)   AS g2 (i),
generate_series(1, p.m) AS gm (i)
ORDER BY
gm.i, g2.i, gn.i ;
``````

In standard SQL - and assuming that there is a reasonable limit on the size of the parameters n, m, i.e. less than a million - you can use a `Numbers` table:

``````CREATE TABLE numbers
( n int not null primary key ) ;
``````

fill it with the preferred method of your DBMS:

``````INSERT INTO numbers (n)
VALUES (1), (2), .., (1000000) ;  -- some mildly complex SQL here
-- no need to type a million numbers
``````

and then use it, instead of `generate_series()`:

``````WITH
parameters (n, m) AS
( VALUES (3, 5) )
SELECT
CASE WHEN g2.i = 1 THEN gn.i ELSE p.n + 1 - gn.i END AS xi
FROM
parameters AS p
JOIN numbers AS gn (i) ON gn.i <= p.n
JOIN numbers AS g2 (i) ON g2.i <= 2
JOIN numbers AS gm (i) ON gm.i <= p.m
ORDER BY
gm.i, g2.i, gn.i ;
``````

If you need plain SQL. Theoretically it should to work on the most DBMSs (tested on PostgreSQL and SQLite):

``````with recursive
s(i,n,z) as (
select * from (values(1,1,1),(3*2,1,2)) as v  -- Here 3 is n
union all
select
case z when 1 then i+1 when 2 then i-1 end,
n+1,
z
from s
where n < 3), -- And here 3 is n
m(m) as (select 1 union all select m+1 from m where m < 2) -- Here 2 is m

select n from s, m order by m, i;
``````

# Explanation

1. Generate series 1..n

Assuming that `n=3`

``````with recursive s(n) as (
select 1
union all
select n+1 from s where n<3
)
select * from s;
``````

It is quite simple and could be found in the almost any docs about recursive CTEs. However wee need two instances of each values so

2. Generate series 1,1,..,n,n

``````with recursive s(n) as (
select * from (values(1),(1)) as v
union all
select n+1 from s where n<3
)
select * from s;
``````

Here we just doubling the initial value, which has two rows, but the second bunch we need in the reverse order, so we'll introduce the order in a bit.

3. Before we introduce the order observe that this is also a thing. We can have two rows in the starting condition with three columns each, our `n<3` is still a single column conditional. And, we're still just increasing the value of `n`.

``````with recursive s(i,n,z) as (
select * from (values(1,1,1),(1,1,1)) as v
union all
select
i,
n+1,
z
from s where n<3
)
select * from s;
``````
4. Likewise, we can mix them up a bit, watch our starting condition change here: here we have a `(6,2)`, `(1,1)`

``````with recursive s(i,n,z) as (
select * from (values(1,1,1),(6,1,2)) as v
union all
select
i,
n+1,
z
from s where n<3
)
select * from s;
``````
5. Generate series 1..n,n..1

The trick here is to generate the series, (1..n) twice, and then simply change the ordering on the second set.

``````with recursive s(i,n,z) as (
select * from (values(1,1,1),(3*2,1,2)) as v
union all
select
case z when 1 then i+1 when 2 then i-1 end,
n+1,
z
from s where n<3
)
select * from s order by i;
``````

Here `i` is order and `z` is number of the sequence (or half of sequence if you want). So for sequence 1 we are increasing order from 1 to 3 and for sequence 2 we are decreasing the order from 6 to 4. And finally

6. Multiply the series to `m`

(see the first query in the answer)

In PostgreSQL, this is easy,

``````CREATE OR REPLACE FUNCTION generate_up_down_series(n int, m int)
RETURNS setof int AS \$\$
SELECT x FROM (
SELECT 1, ordinality AS o, x FROM generate_series(1,n) WITH ORDINALITY AS t(x)
UNION ALL
SELECT 2, ordinality AS o, x FROM generate_series(n,1,-1) WITH ORDINALITY AS t(x)
) AS t(o1,o2,x)
CROSS JOIN (
SELECT * FROM generate_series(1,m)
) AS g(y)
ORDER BY y,o1,o2
\$\$ LANGUAGE SQL;
``````

If you want a portable solution you need to realize that this is basically a mathematical problem.

Given @n as the highest number of the sequence and @x as the position of the number in that sequence (starting with zero), the following function would work in SQL Server:

``````CREATE FUNCTION UpDownSequence
(
@n int, -- Highest number of the sequence
@x int  -- Position of the number we need
)
RETURNS int
AS
BEGIN
RETURN  @n - 0.5 * (ABS((2*((@x % (@n+@n))-@n)) +1) -1)
END
GO
``````

You can check it with this CTE:

``````DECLARE @n int=3;--change the value as needed
DECLARE @m int=4;--change the value as needed

WITH numbers(num) AS (SELECT 0
UNION ALL
SELECT num+1 FROM numbers WHERE num+1<2*@n*@m)
SELECT num AS Position,
dbo.UpDownSequence(@n,num) AS number
FROM numbers
OPTION(MAXRECURSION 0)
``````

(Quick explanation: the function uses MODULO() to create a sequence of repeating numbers and ABS() to turn it into a zig-zag wave. The other operations transform that wave to match the desired result.)

This works in MS-SQL and I think can be modified for any SQL flavor.

``````declare @max int, @repeat int, @rid int

select @max = 3, @repeat = 4

-- create a temporary table
create table #temp (row int)

--create seed rows
while (select count(*) from #temp) < @max * @repeat * 2
begin
insert into #temp
select 0
from (values ('a'),('a'),('a'),('a'),('a')) as a(col1)
cross join (values ('a'),('a'),('a'),('a'),('a')) as b(col2)
end

-- set row number can also use identity
set @rid = -1

update #temp
set     @rid = row = @rid + 1

-- if the (row/max) is odd, reverse the order
select  case when (row/@max) % 2 = 1 then @max - (row%@max) else (row%@max) + 1 end
from    #temp
where   row < @max * @repeat * 2
order by row
``````

Using only basic Math `+ - * /` and Modulo:

``````SELECT x
, s = x % (2*@n) +
(1-2*(x % @n)) * ( ((x-1) / @n) % 2)
FROM (SELECT TOP(2*@n*@m) x FROM numbers) v(x)
ORDER BY x;
``````

This doesn't require a specific RDBMS.

With `numbers` being a number table:

``````...;
WITH numbers(x) AS(
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM (VALUES(0), (0), (0), (0), (0), (0), (0), (0), (0), (0)) AS n0(x)
CROSS JOIN (VALUES(0), (0), (0), (0), (0), (0), (0), (0), (0), (0)) AS n1(x)
CROSS JOIN (VALUES(0), (0), (0), (0), (0), (0), (0), (0), (0), (0)) AS n2(x)
)
...
``````

This generate a number table (1-1000) without using a recursive CTE. See Sample. 2nm must be smaller than the number of row in numbers.

Output with n=3 and m=4:

``````x   s
1   1
2   2
3   3
4   3
5   2
6   1
7   1
8   2
... ...
``````

This version requires a smaller number table (v >= n and v >= m):

``````WITH numbers(v) AS(
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM (VALUES(1), (2), (3), (4), (5), (6), ...) AS n(x)
)
SELECT ord = @n*(v+2*m) + n
, n*(1-v) + ABS(-@n-1+n)*v
FROM (SELECT TOP(@n) v FROM numbers ORDER BY v ASC) n(n)
CROSS JOIN (VALUES(0), (1)) AS s(v)
CROSS JOIN (SELECT TOP(@m) v-1 FROM numbers ORDER BY v ASC) m(m)
ORDER BY ord;
``````

See Sample.

A basic function using iterators.

T-SQL

``````create function generate_up_down_series(@max int, @rep int)
returns @serie table
(
num int
)
as
begin

DECLARE @X INT, @Y INT;
SET @Y = 0;

WHILE @Y < @REP
BEGIN

SET @X = 1;
WHILE (@X <= @MAX)
BEGIN
INSERT @SERIE
SELECT @X;
SET @X = @X + 1;
END

SET @X = @MAX;
WHILE (@X > 0)
BEGIN
INSERT @SERIE
SELECT @X;
SET @X = @X -1;
END

SET @Y = @Y + 1;
END

RETURN;
end
GO
``````

Postgres

``````create or replace function generate_up_down_series(maxNum int, rep int)
returns table (serie int) as
\$body\$
declare
x int;
y int;
z int;
BEGIN

x := 0;
while x < rep loop

y := 1;
while y <= maxNum loop
serie := y;
return next;
y := y + 1;
end loop;

z := maxNum;
while z > 0 loop
serie := z;
return next;
z := z - 1;
end loop;

x := x + 1;
end loop;

END;
\$body\$ LANGUAGE plpgsql IMMUTABLE STRICT;
``````

A way to do it in SQL Server using a recursive cte.

1. Generate the required number of members in the series (for n=3 and m=4 it would be 24 which is 2nm)

2. After that using logic in a `case` expression, you can generate the required series.

`Sample Demo`

``````declare @n int=3;--change the value as needed
declare @m int=4;--change the value as needed

with numbers(num) as (select 1
union all
select num+1 from numbers where num<2*@n*@m)
select case when (num/@n)%2=0 and num%@n<>0 then num%@n
when (num/@n)%2=0 and num%@n=0 then 1
when (num/@n)%2=1 and num%@n<>0 then @n+1-(num%@n)
when (num/@n)%2=1 and num%@n=0 then @n
end as num
from numbers
OPTION(MAXRECURSION 0)
``````

As suggested by @AndriyM .. the `case` expression can be simplified to

``````with numbers(num) as (select 0
union all
select num+1 from numbers where num<2*@n*@m-1)
select case when (num/@n)%2=0 then num%@n + 1
when (num/@n)%2=1 then @n - num%@n
end as num
from numbers
OPTION(MAXRECURSION 0)
``````

`Demo`

``````declare @n int = 5;
declare @m int = 3;
declare @t table (i int, pk int identity);
WITH  cte1 (i)
AS ( SELECT 1
UNION ALL
SELECT i+1 FROM cte1
WHERE  i < 100  -- or as many you might need!
)
insert into @t(i) select i from cte1 where i <= @m  order by i
insert into @t(i) select i from @t order by i desc
select t.i --, t.pk, r.pk
from @t as t
cross join (select pk from @t where pk <= @n) as r
order by r.pk, t.pk
``````

# C Extension: `pg-generate-up-down-series`

To beat the benchmark I decided to resubmit with a C Extension. I published it under the name of `pg-generate-up-down-series`. It's only slightly faster than Verace's query, and slightly slower than `generate_series()`.

## Code

You can see the FULL code for the function in question here, this is a breakdown of the logic/flow,

``````Datum generate_up_down_series_4(PG_FUNCTION_ARGS);
PG_FUNCTION_INFO_V1(generate_up_down_series_4);

typedef struct
{
# used in ValuePerCallMode for state between calls
} fctx_4;

Datum
generate_up_down_series_4(PG_FUNCTION_ARGS)
{

if (SRF_IS_FIRSTCALL()) {
# get arguments

ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;

# if in the `FROM` clause use Materialized convention
if ( rsinfo->allowedModes & SFRM_Materialize_Preferred ) {
}
# if in the `SELECT` clause do initialize for ValuePerCall convention
else {
FuncCallContext *funcctx = SRF_FIRSTCALL_INIT();
MemoryContext oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);

# pre-generate one series and place it in memory

MemoryContextSwitchTo(oldcontext);
}

}

# On every call do this stuff.
FuncCallContext *funcctx = SRF_PERCALL_SETUP();

if ( fctx->current <= fctx->n2 ) {
# Get data and return a tuple/row
SRF_RETURN_NEXT(funcctx, result);
}
else {
# Say we're done.
SRF_RETURN_DONE(funcctx);
}

# this will never be reached, but it's a good convention
PG_RETURN_NULL();
}
``````

## Benchmark

``````VERACE_QUERY [number to beat]
count
--------
387600
(1 row)

Time: 17.732 ms

XXXXX VALUEPERCALL XXXXX
SELECT count(*) FROM (SELECT generate_series(1,2*340*570)) AS t;
count
--------
387600
(1 row)

Time: 15.784 ms
SELECT count(*) FROM (SELECT generate_up_down_series_evan(340,570)) AS t;
count
--------
387600
(1 row)

Time: 16.612 ms

XXXXX MATERIALIZED XXXXX
SELECT count(*) FROM (SELECT * FROM generate_series(1,2*340*570)) AS t;
count
--------
387600
(1 row)

Time: 45.261 ms
SELECT count(*) FROM (SELECT * FROM generate_up_down_series_evan(340,570)) AS t;
count
--------
387600
(1 row)
``````

ORACLE macro:

``````CREATE OR REPLACE FUNCTION genupdownseq(p_num IN NUMBER, p_cycle IN NUMBER, p_up IN NUMBER DEFAULT 1)
RETURN VARCHAR2
SQL_MACRO
IS
BEGIN
RETURN q'{select
case when mod(trunc((n-1)/p_cycle)+1 ,2) = p_up
then
mod(n-1,p_cycle)+1
else
p_cycle - mod(n-1,p_cycle)
end as n
from (
select level as n from dual connect by level <= p_num
)
}';
END ;
/

select * from genupdownseq(200,10) ;

1
2
3
4
5
6
7
8
9
10
10
9
8
7
6
...

select * from genupdownseq(20,5,0) ;

5
4
3
2
1
1
2
3
4
5
5
4
...
``````