SQL sort Linked List

Question

I have a SQL linked list in a single table. I need a query that will return all rows sorted using the previous_item_id to grab the previous row and correctly order the linked list. This SQL query will be used in PHP.

*NOTE: I'm trying to sort a LINKED LIST, not simply ordering by the previous_item_id. So not this:

SELECT * FROM $table_name ORDER BY previous_item_id

I'm a novice at SQL queries, please help :)

Table:

ID        EMAIL              PREVIOUS_ITEM_ID
1         test@gmail.com     NULL
2         test2@gmail.com    1
3         test3@gmail.com    4
4         test4@gmail.com    2
5         test5@gmail.com    3

The sorted rows should return:

test@gmail.com
test2@gmail.com
test4@gmail.com
test3@gmail.com
test5@gmail.com

Please don’t tag multiple different database systems without explaining why; questions on sql seldom refer to more than one. You also don’t seem to state what problem you’re getting with your query? — Caius Jard, Oct 07 '18 at 04:34
Sorting the linked list with a CTE or something is the question. Ive been stuck on this all day so a bit of help sorting the linked list is the question — rwchampin, Oct 07 '18 at 04:37
Oracle: [LEAD and LAG analytic functions](https://oracle-base.com/articles/misc/lag-lead-analytic-functions) — Bob Jarvis - Слава Україні, Oct 07 '18 at 04:45
This is either MySQL or oracle. Remove the tag that it isn’t. — Caius Jard, Oct 07 '18 at 04:51
For mysql see https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query — gherkin, Oct 07 '18 at 04:55
@CaiusJard I have not posted a solution "That I said I came up with", if you read the question, I asked for help creating a query to PRODUCE the results grid I gave. — rwchampin, Oct 07 '18 at 04:56
Possible duplicate of [Connect By Prior Equivalent for MySQL](https://stackoverflow.com/questions/7631048/connect-by-prior-equivalent-for-mysql) — Caius Jard, Oct 10 '18 at 05:02
You’re not sorting your linked list, you’re actually linking it up. What you have need for here is a hierarchical query, see the comment above for some solutions. If you’re willing to change your data storage you could make this a lot easier by storing your list as an id(repeated for every node in a list) and node index(unique incrementing from start to end of list) — Caius Jard, Oct 10 '18 at 05:03

score 0 · Answer 1 · answered Oct 07 '18 at 04:57

Try like below if your data is alsways number after 4 character use SUBSTRING_INDEX and substring for find out number and then order it

SELECT * FROM $table_name
order by  substring( SUBSTRING_INDEX(email, '@', 1),5,5)

example

with t1 as
(
select 'test2@gmail.com' as email
union all
select 'test1@gmail.com'
)select * from t1 order by substring( SUBSTRING_INDEX(email, '@', 1),5,5) 

   email
test1@gmail.com
test2@gmail.com

score 0 · Answer 2 · answered Oct 10 '18 at 05:19

I think I’d be tempted to solve this in the front end code, not sure what language you’re running but using a pseudo code:

LinkedList a = new LinkedList
ListNode n = a
result = query(‘select * from table where prev is null”)
while result.hasrows
    n.data = result.email
    result = query(‘select * from table where prev =‘ + result.id)
    if result.hasrows
        n.nextnode = new Node
        n = n.nextnode
    end if
end while

If you’re determined to do it in MySQL, it would probably be cleanest and most maintainable to do it as a stored procedure. You’ll find an example in the SO page I linked in my comment in the question

SQL sort Linked List

2 Answers2