skip to Main Content

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

2

Answers


  1. 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  
    
    Login or Signup to reply.
  2. 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;
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search