How to build a sequence of values by combining two columns?

Question

I am looking to get a sequence of values by combining two columns that are linked using some random ids:

Table (col2 and col3 are linked)

col1   col2    col3
aa     a144    d653 
bb     z567    a144
cc     d653    h999
dd     y678    z567

The two columns (col2 and col3), this is like a chain that is forming up.

The result I am looking for is a sequence from start to end:

sequence
y678
z567
a144
d653
h999

Explanation:

The sequence starts at row 4 (dd,y678,z567), followed by row 2 (bb,z567,a144) and so on. Col3 id is the reference for the Col2 id, to decide the next element.

This version does not support CTE. Iterative stored procedure only. — Akina, Mar 04 '22 at 16:06

Robert Synoradzki · Accepted Answer · 2022-03-10T09:06:03.673

What you're looking for is a recursive query.

Assuming your table is called data, you do it like this:

WITH RECURSIVE query(id) AS (
    SELECT col2
    FROM data
    WHERE col1 = 'dd' -- Select the initial row here

    UNION ALL

    SELECT data.col3
    FROM data
    INNER JOIN query on query.id = data.col2
)
SELECT *
FROM query;

Tested snippet available here: https://onecompiler.com/mysql/3xvj2a47v.

This syntax works in MySQL version 8 and up. If your version is lower, first thing I would recommend is to update it, if possible. If not possible, consult this answer for a workaround using MySQL 5: https://stackoverflow.com/a/33737203/2979473.

score 0 · Answer 2 · answered Mar 04 '22 at 16:31

0

you are going to have to use a cursor.. https://www.mysqltutorial.org/mysql-cursor/

first step will be to select the value from col2 that doesn't exist in col3 then insert the value from col3 where the current variable is in col2 return the results set when the value in col3 is not found in col2

This will only work if there is one start and end value and one distinct path through the chain. It will also be slow, because this is not how RDBMS databases are designed to work.

answered Mar 04 '22 at 16:31

Aaron Reese

544
6
18

Cursor would work, but is an overkill. In this situation it is avoidable. – Robert Synoradzki Mar 08 '22 at 21:54
@RobertSynoradzki How would you do it without a cursor? His version of MySQL does not support recursive CTEs? You could join the table to itself using source.col2 = target.col3 as demoed in the recursive solution above , but I don't see any way to get the HEAD of the chain and a sequence step number without using a cursor. – Aaron Reese Mar 09 '22 at 10:18
Oh, I didn't notice this version was 5.7. Sorry for my comment then. Although this answer https://stackoverflow.com/a/33737203/2979473 provides some CURSORless alternative that uses assigning a variable inside the query, I really wouldn't recommend it as it reeks of indeterminism. CURSOR might be the only safe way to go... unless MySQL engine update is possible, in which case I would totally go for ditching the ancient version. That would probably solve more problems than one :) – Robert Synoradzki Mar 09 '22 at 13:10

score 0 · Answer 3 · edited Mar 04 '22 at 18:34

0

I think this query will work for you.

SELECT DISTINCT SEQ 
FROM 
(
  SELECT COL2 SEQ FROM TABLE1 
  UNION 
  SELECT COL3 SEQ FROM TABLE1
) ORDER BY 1

edited Mar 04 '22 at 18:34

ouflak

2,458
10
44
49

answered Mar 04 '22 at 17:00

AmanJ

21
3

I also need to ensure the order is maintained from start to end, how to ensure the order is also maintained? – Bhushan Pant Mar 05 '22 at 02:13
Can you specify the logic of the sequence? I need to understand the logic by which the output sequence is generated – AmanJ Mar 05 '22 at 05:21
Col2 and Col3 are connected using random ids. If you can see the sequence starts at (dd , y678 ,z567), followed by (bb,z567, a144). Col3 id is the next id for Col2. – Bhushan Pant Mar 05 '22 at 16:07
I think that is coincidental that it works. Bhushan, change the letters so that this won't work. – Rick James Mar 08 '22 at 00:46
@AmanJ - Move `DISTINCT` to `UNION DISTINCT`. – Rick James Mar 08 '22 at 00:47
Some tasks are better done in the application language than in SQL. – Rick James Mar 08 '22 at 00:48
`UNION` does not solve the problem - it just concatenates two sets of results (items from `col2`, then from `col3` returned as one column). What the OP wants is to print `col2`, then remember the value in `col3`, then go to row whose `col2` is that value - and repeat. – Robert Synoradzki Mar 08 '22 at 21:50

How to build a sequence of values by combining two columns?

3 Answers3