Can MySQL retrieve multiple rows for one row in a database?

codename_47
May 11, 2023
266 views
0 votes
2 Answers

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

How can I achieve this in mysql

Tags: mysql sql

Answers

ysth
May 11, 2023 at 7:03 am
0 votes

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

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

- leopintos11
- May 11, 2023 at 7:38 am
- 0 votes
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;
```
Login or Signup to reply.