Limit sequential number in SELECT

Question

I need a SELECT statement with a sequential number N, where N is limited, let's say to less then 5.

With this query

SELECT 'A' a, @nr:=@nr+1 AS c
    FROM (SELECT @nr:=0) r
WHERE @nr < 5

I only get one result

 a | c
---+---
 A | 1

where I hoped on this result:

 a | c
---+---
 A | 1
 A | 2
 A | 3
 A | 4

So - if possible at all - what do I need to change to get the desired result?

Before MySQL 8.0, try somethingn from [Generate an integer sequence in MySQL](https://stackoverflow.com/q/304461). Your query doesn't work because you cannot generate rows from nothing. You have one row (the one you create in your inner select), you have no join/union, so you end up with at most one row. — Solarflare, Nov 08 '20 at 13:03

score 1 · Answer 1 · answered Nov 08 '20 at 12:07

1

Using recursive CTE:

WITH RECURSIVE cte(a,c) AS (
  SELECT 'A', 1
  UNION ALL
  SELECT a, c + 1
  FROM cte
  WHERE c + 1 < 5
)
SELECT *
FROM cte;

db<>fiddle demo

answered Nov 08 '20 at 12:07

Lukasz Szozda

162,964
23
234
275

Sorry, should have mentioned the the mysql version. `WITH` is only available from version 8. Any chance of doing this with an older version? – Michel Nov 08 '20 at 12:18

Booboo · Accepted Answer · 2020-11-08T13:28:41.753

The comment made by @Solarflare is on point, i.e. you need to join your relation with another one that has at least 5 rows.

You need to first create a dummy table of 2**N rows with column id and integer values 1, 2, 3, ... 2**N. The following would, for example, create a table of 2**20 rows (see this):

set @i = 0;
drop TEMPORARY table if exists dummyids;
create TEMPORARY table dummyids
    select @i := @i + 1 as id
    from (select true union all select true) t0
    join (select true union all select true) t1
    join (select true union all select true) t2
    join (select true union all select true) t3
    join (select true union all select true) t4
    join (select true union all select true) t5
    join (select true union all select true) t6
    join (select true union all select true) t7
    join (select true union all select true) t8
    join (select true union all select true) t9
    join (select true union all select true) t10
    join (select true union all select true) t11
    join (select true union all select true) t12
    join (select true union all select true) t13
    join (select true union all select true) t14
    join (select true union all select true) t15
    join (select true union all select true) t16
    join (select true union all select true) t17
    join (select true union all select true) t18
    join (select true union all select true) t19
;
select * from dummyids;

Since you only need 5 rows, let's create a table of 8 rows:

set @i = 0;
drop TEMPORARY table if exists dummyids;
create TEMPORARY table dummyids
    select @i := @i + 1 as id
    from (select true union all select true) t0
    join (select true union all select true) t1
    join (select true union all select true) t2
;
select * from dummyids;

Now all you need to do is join your original relation with another relation that has at least 5 rows (which we have conveniently created above):

SELECT 'A' a, @nr:=@nr+1 AS c
FROM (SELECT @nr:=0) r join dummyids
WHERE @nr < 5;

Now I recognize you could have taken the numbers directly from the dummyids table:

SELECT 'A' a, dummyids.id c
FROM dummyids
WHERE id <= 5
ORDER BY id;

But I wanted to show a technique that could be used when you are using MySql variables in a more complicated way.

Thanks for explaining. I ended up doing something similar. I have a table with enough rows for my requirement, so I used that with sequential numbering to get the numbers I need. — Michel, Nov 09 '20 at 05:45

Limit sequential number in SELECT

2 Answers2