0

I have a select query

select id from tags

Which returns data like

id
--
1
2

I want to get data with a sequence number as shown below

id seq
-- ---
1  1
1  2
1  3
1  4
1  5
2  1
2  2
2  3
2  4
2  5

How can I achieve this in mysql

codename_47
  • 449
  • 2
  • 17
  • `select id,seq from tags` maybe? you are not showing the structure of your table.. so we need to guess? Please take the [tour]. See also [ask] and in your Question add a [mre] – Ron May 11 '23 at 04:59
  • Sequence is not present in tag table, lets assume that tag table only has one colum named tag_id – codename_47 May 11 '23 at 05:03

2 Answers2

2
select id, seq
from tags
cross join (
    select 1 seq union all select 2 union all select 3 union all select 4 union all select 5
) seqs
order by id, seq

Or

with recursive seqs as (
    select 1 seq
    union all
    select seq+1
    from seqs
    where seq < 5
)
select id, seq
from tags
cross join seqs  
order by id, seq
ysth
  • 96,171
  • 6
  • 121
  • 214
0

You can use an incremental variable as specified here;

In your case, the query should be like this:

SET @rowcount=0;
SELECT id, @rowcount:=@rowcount+1 as seq from tags;
leopintos11
  • 11
  • 2