[FIXED] How can I replace nested for statement with mysql query?

Issue

Environment :

• MySQL 5.7.x
• Spring MVC

Table Data (name: TableA)

seq	level	name	order	parent_seq
1	1	name1	1	0
2	1	name2	2	0
3	2	sub1-1	1	1
4	2	sub1-2	2	1
5	2	sub2-1	1	2
6	3	third-2-1	1	5
7	3	third-1-1	1	3

Expected Result

seq	level	name	order	parent_seq	next_level
1	1	name1	1	0	2
3	2	sub1-1	1	1	3
7	3	third-1-1	1	3	2
4	2	sub1-2	2	1	1
2	1	name2	2	0	2
5	2	sub2-1	1	2	3
6	3	third-2-1	1	5	1 (last default value: 1)

Now I'm genenrating expected result with nested for statement(JAVA). Is there any way to generate expected result only with MySQL Query?

The data stacked in random order in the table is sorted by ASC based on the level column, but check the parent_seq column so that it is sorted under the parent data. And if there are multiple data of the same level, sort by ASC based on the sort column value.

Thanks in advance!

++ EmbraceNothingButFuture's answer was great, but the query seems to work on MySQL 8. I'm using MySQL 5.7. Is there any way to use the query on MySQL 5.7?

Solution

Summary:

• Use REGEXP_SUBSTR(name,"[0-9]+\-?[0-9]*") to extract the numbers and sort the datas using the numbers.

• For MySQL v8 above, you can use LEAD() to generate the "next_level" column based on the "level" column

• COALESCE() function for the last default value = 1

SELECT 
    t1.*, 
    COALESCE(LEAD(t1.level, 1) OVER(ORDER BY REGEXP_SUBSTR(name,"[0-9]+\-?[0-9]*")), 1) AS next_level
FROM TableA t1
ORDER BY REGEXP_SUBSTR(name,"[0-9]+\-?[0-9]*"), t1.level

See db<>fiddle

Answered By - EmbraceNothingButFuture
Answer Checked By - Clifford M. (JavaFixing Volunteer)