CODEWITHSUNDEEP
mysqlsqlmariadb
Fact
Fact

Reputation: 2460

Concatenate previous all rows until current row

I am trying to form a mysql query where I want to concat all the previous values until the current row -1 . For example

select * from a;
+------+
| id   |
+------+
|    1 |
|    3 |
|    4 |
+------+

Desired o/p

+------+============
| id   |concat_prev_to_cur
+------+============
|    1 |null
|    3 |1
|    4 |1,3
+------+============

Can this be achieved with using SELECT only

Tried this but this doesn't work

with recursive b as (select id from a union all select concat(a.id,',',b.id) from b join a on a.id=b.id) select * from b;

Update: This seems to be close to the desired output

With b as (Select id, row_number() Over(order by id) r from a) select c.id,group_concat(b.id) from b join b c on b.r < c.r group by c.r ;
+------+--------------------+
| id   | group_concat(b.id) |
+------+--------------------+
|    3 | 1                  |
|    4 | 1,3                |
+------+--------------------+

Upvotes: 0

Views: 198

Answers (1)

id&#39;7238
id&#39;7238

Reputation: 2631

Maybe a recursive query is not needed.

SELECT id, (
    SELECT GROUP_CONCAT(id) AS ids
    FROM a AS agg 
    WHERE agg.id < a.id
) AS ids
FROM a

db<>fiddle

Upvotes: 2

Related Questions